Use Values from Other Tables in UPDATE in Oracle


How To Use Values from Other Tables in UPDATE Statements in Oracle?



If you want to update values in one with values from another table, you can use a subquery in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clause. The tutorial exercise below shows a good example:

UPDATE fyi_links SET (notes, created) = 
  (SELECT last_name, hire_date FROM employees
    WHERE employee_id = id)
  WHERE id < 110;
3 rows updated.

SELECT * FROM fyi_links WHERE id < 110;
  ID URL                      NOTES      COUNTS CREATED
---- ------------------------ --------- ------- ---------
 101 Kochhar       999 21-SEP-89
 102 De Haan         0 13-JAN-93
 103 Hunold    NULL    03-JAN-90

This statement updated 3 rows with values from the employees table.


Error: Single-Row Subquery Returns More Than One Row in Oracle

Use Existing Column Values in SET Clause in Oracle

Understanding SQL DML Statements for Oracle

⇑⇑ Oracle Database Tutorials

2020-01-21, 1206👍, 0💬