Update with join

Using updates with joins is something that does not work the first time. I’ve been using Oracle, MSSQL and other DB’s before I became Oracle DBA and I always had to check resources to get it done. Now I add an other resource….

Here is an example of updating values while joining for a certein employee. You set the amount of only the records you select:

update (
select * from salary s
join employee e on s.emp_id = e.emp_id
where e.name = 'scott'
)
set s.amount = s.amount * 1.1;

Next we’ll see an example of a join and updating more fields a once:

update salary s
set (s.amount, s.bonus)
(
select d.default_amount, d.default_bonus
from department d
join employee e on e.dept_no = d.dept_no
where s.emp_id = e.emp_id
);

Hope this helps clearing up things.

Happy consulting!