Select rows to update

Either form of the UPDATE statement can end with a WHERE clause that determines which rows are modified. If you omit the WHERE clause, all rows are modified. To select the precise set of rows that need changing in the WHERE clause can be quite complicated. The only restriction on the WHERE clause is that the table that you update cannot be named in the FROM clause of a subquery.

The first form of an UPDATE statement uses a series of assignment clauses to specify new column values, as the following example shows:

UPDATE customer
   SET fname = 'Barnaby', lname = 'Dorfler'
   WHERE customer_num = 103;

The WHERE clause selects the row you want to update. In the demonstration database, the customer.customer_num column is the primary key for that table, so this statement can update no more than one row.

You can also use subqueries in the WHERE clause. Suppose that the Anza Corporation issues a safety recall of their tennis balls. As a result, any unshipped orders that include stock number 6 from manufacturer ANZ must be put on back order, as the following example shows:

UPDATE orders
   SET backlog = 'y'
   WHERE ship_date IS NULL
   AND order_num IN
      (SELECT DISTINCT items.order_num FROM items
         WHERE items.stock_num = 6
         AND items.manu_code = 'ANZ');

This subquery returns a column of order numbers (zero or more). The UPDATE operation then tests each row of orders against the list and performs the update if that row matches.