How To Use Values from Other Tables in UPDATE Statements?

Answer 1

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 temp_links SET (notes, created) =
  (SELECT last_name, hire_date FROM employees
    WHERE employee_id = id)
  WHERE id < 110;
3 rows updated.

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

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