Chapter 7: Adding, Changing, and Deleting Data

Using the from clause with update

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

For example, earlier in this chapter, an example was given for inserting some 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. When one of these authors, Dirk Stringer, writes a book, The Psychology of Computer Cooking, a title identification number is assigned to his book in the titles table. 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 that 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.

View this book as PDF