Hi,
I got a problem updating a table with a value coming from two other joined tables. It looks like if I enter a value for an employee number, it does update it, but when I assign a field name, it gives me an error, I guess because all of my tables contain multiple records for the same employee. The following works:
MERGE INTO table1 a
USING
(SELECT b.employee as EMPNO, numofdays as NEWDAYS
FROM table2 b, table3 c
WHERE b.employee = c.employee
AND b.sequence = c.sequence
AND b.year = '2015') d
ON (a.employee = d.EMPNO)
WHEN MATCHED THEN
UPDATE SET a.days = NEWDAYS
WHERE school_year = '2014-2015'
and a.employee = 123456;
The following does not work, and the only difference is that I'm assigning a variable name instead of the actual value. I also tried it without specifying "and a.employee = EMPNO;", as I'm already linking table1 to the select on the statement "ON (a.employee = d.EMPNO)".
MERGE INTO table1 a
USING
(SELECT b.employee as EMPNO, numofdays as NEWDAYS
FROM table2 b, table3 c
WHERE b.employee = c.employee
AND b.sequence = c.sequence
AND b.year = '2015') d
ON (a.employee = d.EMPNO)
WHEN MATCHED THEN
UPDATE SET a.days = NEWDAYS
WHERE school_year = '2014-2015'
and a.employee = EMPNO;
I tried the UPDATE unsuccessfully as well.
How else could I accomplish this?
Thank you.