Update with uniform values

Each assignment after the keyword SET specifies a new value for a column. That value is applied uniformly to every row that you update. In the examples in the previous section, the new values were constants, but you can assign any expression, including one based on the column value itself. Suppose the manufacturer code HRO has raised all prices by five percent, and you must update the stock table to reflect this increase. Use the following statement:

UPDATE stock
   SET unit_price = unit_price * 1.05
   WHERE manu_code = 'HRO';

You can also use a subquery as part of the assigned value. When a subquery is used as an element of an expression, it must return exactly one value (one column and one row). Perhaps you decide that for any stock number, you must charge a higher price than any manufacturer of that product. You need to update the prices of all unshipped orders. The SELECT statements in the following example specify the criteria:

UPDATE items
   SET total_price = quantity *
      (SELECT MAX (unit_price) FROM stock
         WHERE stock.stock_num = items.stock_num)
   WHERE items.order_num IN
      (SELECT order_num FROM orders
         WHERE ship_date IS NULL);

The first SELECT statement returns a single value: the highest price in the stock table for a particular product. The first SELECT statement is a correlated subquery because, when a value from items appears in the WHERE clause for the first SELECT statement, you must execute the query for every row that you update.

The second SELECT statement produces a list of the order numbers of unshipped orders. It is an uncorrelated subquery that is executed once.