UPDATE statement

<< Click to Display Table of Contents >>

Navigation:  Local SQL > Data Manipulation Language > Statements >

UPDATE statement

Previous pageReturn to chapter overviewNext page

 

Modifies one or more existing rows in a table.

UPDATE table_reference

SET column_ref = update_atom [, column_ref = update_atom...]

[WHERE predicates]

Description

Use the UPDATE statement to modify one or more column values in one or more existing rows in a single table per statement.

Use a table reference in the UPDATE clause to specify the table to receive the data changes.

The SET clause is a comma-separated list of update expressions. Each expression comprises the name of a column, the assignment operator (=), and the update value (update atom) for that column. The update atoms in any one update expression may be literal values, singleton return values from a SELECT subquery, or calculated values. Subqueries supplying an update atom for an update expression must return a singleton result set (one row) and return only a single column.

UPDATE SalesInfo

SET TaxRate = 0.0825

When update atoms come from a SELECT subquery, one SELECT statement must be provided for each column being updated in the target table. Each SELECT subquery providing an update atom must be enclosed in parentheses. The following statement updates the two columns OnHand and InventoryDate in the table INVENTORY, each with a separate SELECT subquery.

UPDATE Inventory

SET OnHand = OnHand –

 (SELECT SUM(Orders.QtySold)

 FROM Orders

 WHERE (Orders.PartNo = Inventory.PartNo) AND

   (Orders.OrderDate BETWEEN "10/01/1999" AND "10/31/1999")),

 InventoryDate = OnHand –

 (SELECT MAX(Orders.OrderDate)

 FROM Orders

 WHERE (Orders.PartNo = Inventory.PartNo) AND

   (Orders.OrderDate BETWEEN "10/01/1999" AND "10/31/1999")),

The optional WHERE clause restricts updates to a subset of rows in the table. If no WHERE clause is specified, all rows in the table are updated using the SET clause update expressions.

UPDATE SalesInfo

SET TaxRate = 0.0825

WHERE (State = "CA")

The UPDATE statement only supports SELECT subqueries in the WHERE clause. References to tables other than the one in which rows are update or columns in such tables are only possible in SELECT subqueries.