UPDATE Examples

For example, Reginald Blotchet-Halls decides to change his name to Goodbody Health. Here's how to change his row in the authors table:

UPDATE authors
SET au_lname = 'Health', au_fname = 'Goodbody'
WHERE au_lname = 'Blotchet-Halls'

This example that updates the ytd_sales column of the titles table to reflect the most recent sales recorded in the sales table:

UPDATE titles
SET ytd_sales = ytd_sales  qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
    AND sales.ord_date in (SELECT MAX(sales.ord_date) FROM sales)

The preceding example assumes that only one set of sales is recorded for a given title on a given date and that updates are up to date. If this is not the case (if more than one sale for a given title can be recorded on the same day), then this example does not work correctly. It executes without error, but each title is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row twice.

In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown here:

UPDATE titles
SET ytd_sales = (SELECT SUM(qty)
    FROM sales
    WHERE sales.title_id = titles.title_id
        AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales