Updating a table using a scalar-subselect

Using a scalar-subselect, you can update one or more columns in a table with one or more values selected from another table.

In the following example, an employee moves to a different department but continues working on the same projects. The employee table has already been updated to contain the new department number. Now the project table needs to be updated to reflect the new department number of this employee (employee number is '000030').

UPDATE PROJECT
  SET DEPTNO =
        (SELECT WORKDEPT FROM EMPLOYEE
           WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO)
  WHERE RESPEMP='000030'

This same technique can be used to update a list of columns with multiple values returned from a single select.