Difference between revisions of "Oracle/Update join"
Jump to navigation
Jump to search
| Line 10: | Line 10: | ||
Views and selects with joins may only be updated when each row can be identified by a uique ID, so the join MUST be performed using key columns! In the above case, ''emloyee_id'' it the key column. | Views and selects with joins may only be updated when each row can be identified by a uique ID, so the join MUST be performed using key columns! In the above case, ''emloyee_id'' it the key column. | ||
'''For cases where no explicit key columns exist, there are some alternatives e.g. using the | '''For cases where no explicit key columns exist, there are some alternatives e.g. using the [[Oracle/Merge|MERGE]] statement:''' | ||
<source lang="oracle11"> | <source lang="oracle11"> | ||
MERGE INTO t_employees e | MERGE INTO t_employees e | ||
| Line 18: | Line 18: | ||
UPDATE SET e.first_name = u.first_name, e.last_name = u.last_name; | UPDATE SET e.first_name = u.first_name, e.last_name = u.last_name; | ||
</source> | </source> | ||
[[Category:Oracle]] | [[Category:Oracle]] | ||
Latest revision as of 10:04, 7 December 2009
Update values in a Table using a join:
UPDATE (SELECT b.bonus, e.salary
FROM employee_bonus b
JOIN employees e ON b.employee_id = e.employee_id
WHERE e.bonus_eligible = 1) t
SET t.bonus = 0.1 * t.salary;
Views and selects with joins may only be updated when each row can be identified by a uique ID, so the join MUST be performed using key columns! In the above case, emloyee_id it the key column.
For cases where no explicit key columns exist, there are some alternatives e.g. using the MERGE statement:
MERGE INTO t_employees e
USING (SELECT email, fist_name, last_name FROM t_user) u
ON (u.email = e.email and u.email is not null) -- email is not a key column!
WHEN MATCHED THEN -- update existing
UPDATE SET e.first_name = u.first_name, e.last_name = u.last_name;