Use the from Clause with update

Use the from clause to add data from one or more tables into the table you are updating.

In this example, an author by the name of Dirk Stringer writes a book called The Psychology of Computer Cooking. The data was created by inserting new rows into the titleauthor table for authors without titles, filling in the au_id column, and using dummy or null values for the other columns. You can modify his row in the titleauthor table by adding a title identification number for him:

update titleauthor
set title_id = titles.title_id
from titleauthor, titles, authors
    where titles.title =
    "The Psychology of Computer Cooking"
    and authors.au_id = titleauthor.au_id
    and au_lname = "Stringer"

An update without the au_id join changes all the title_ids in the titleauthor table so that they are the same as The Psychology of Computer Cooking ’s identification number. If two tables are identical in structure, except one has NULL fields and some null values, and the other has NOT NULL fields, you cannot insert the data from the NULL table into the NOT NULL table with a select. In other words, a field that does not allow NULL cannot be updated by selecting from a field that does, if any of the data is NULL.

As an alternative to the from clause in the update statement, you can use a subquery, which is ANSI-compliant.