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.