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 http://dev.fyicenter.com Kochhar       999 21-SEP-89
 102 http://dba.fyicenter.com De Haan         0 13-JAN-93
 103 http://sqa.fyicenter.com Hunold    NULL    03-JAN-90

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

⇒Understanding SQL DML Statements for Oracle

⇒⇒Oracle Database Tutorials