How can I combine 2 joins in an update?

Question

The code below (currently with error) would update all rows in the table, but I'm aiming for that the code should only update the p.hour of the per_id in the where clause and not all rows.

It's important that the value for the set is a column form table worker and one from table department

update worker
set p_hour = p_hour + a.hour
where exists
(select
p.per_id,
p.p_hour
from
worker p,
department a
where
p.per_id = a.per_id and
p.per_id = '1234')

This is my current error:

ERROR at line 2: ORA-00904: "a.hour": invalid identifier

you can't reference a table in `set` from the `where` clause see https://stackoverflow.com/a/7031405/1398418 for alternatives — Oleg, Aug 14 '17 at 20:09

score 0 · Answer 1 · answered Aug 14 '17 at 20:18

The canonical way in Oracle is:

update worker w
    set p_hour = (p_hour +
                  (select d.hour
                   from department d
                   where w.per_id = d.per_id
                  )
                 )
    where w.per_id = '1234' and
          exists (select 1
                  from department d
                  where w.per_id = d.per_id
                 );

You can also use merge.

score 0 · Answer 2 · answered Aug 14 '17 at 22:47

You can also use an updateable join view, inline or otherwise:

update (
         select e.employee_id, e.salary, d.department_name
         from   employees e
                join departments d on d.department_id = e.department_id
       )
set    salary = salary * 10
where  department_name = 'Finance';

For this to be allowed, the view has to be key-preserved. In this example, we can update employees because departments.department_id has a unique key or index.

How can I combine 2 joins in an update?

2 Answers2