Updating Data (Embedded SQL Guide)

Go Up to Working with Data

To change values for existing rows of data in a table, use the UPDATE statement. To update a table, a user or procedure must have UPDATE privilege for it. The syntax of UPDATE is:

UPDATE [TRANSACTION name] table
SET col = <assignment> [, col = <assignment> ...]
WHERE <search_condition> | WHERE CURRENT OF cursorname
[ORDER BY <order_list>]
[ROWS <value> [TO <upper_value>] [BY <step_value>][PERCENT][WITH TIES]];

UPDATE changes values for columns specified in the SET clause; columns not listed in the SET clause are not changed. A single UPDATE statement can be used to modify any number of rows in a table. For example, the following statement modifies a single row:

EXEC SQL
UPDATE DEPARTMENT
SET DEPARTMENT = 'Publications'
WHERE DEPARTMENT = 'Documentation';

The WHERE clause in this example targets a single row for update. If the same change should be propagated to a number of rows in a table, the WHERE clause can be more general. For example, to change all occurrences of “Documentation” to “Publications” for all departments in the DEPARTMENT table where DEPARTMENT equals “Documentation,” the UPDATE statement would be as follows:

EXEC SQL
UPDATE DEPARTMENT
SET DEPARTMENT = 'Publications'
WHERE DEPARTMENT = 'Documentation';

Using UPDATE to make the same modification to a number of rows is sometimes called a mass update, or a searched update.

The WHERE clause in an UPDATE statement can contain a subquery that references one or more other tables. For a complete discussion of subqueries, see Using Subqueries.

Topics

Advance To:

Updating Multiple Rows