SQL Pocket Guide, 3rd Edition

Updating Data

To modify existing data in a table, use the UPDATE statement. You can update one row or many rows, you can specify a single set of new values in the statement, or you can generate new values through a subquery.

Simple Updates

A simple UPDATE takes the following form:

UPDATE table
SET column = value, column = value ...
WHERE predicates

In this form, predicates identifies one or more rows that you want to update. You can specify as many column = value pairs as you like—one for each column you want to modify:

UPDATE upfall
SET owner_id = 1
WHERE name = 'Munising Falls';

When you specify only one new value, you will usually want to update only one row, and your WHERE-clause predicates should reference primary or unique key values to identify that row. Using expressions, you can write sensible UPDATEs that modify many rows. The following example works in DB2:

UPDATE upfall
SET datum = UPPER(datum),
    lat_lon = TRIM(UPPER(lat_lon));

This example also demonstrates the use of the comma to separate multiple-column updates in a SET clause.

Note

In MySQL, if you are updating a self-referential foreign key or its related primary key, you should include an ORDER BY clause at the end of your update to control the order in which rows are updated. For more on this issue, see Deleting in Order and Subquery Inserts.

New Values from a Subquery

You can also generate new values from a subquery. One way to do this is to write separate subqueries for each column that you are updating:

UPDATE table SET ...