2.32 UPDATE

---

Description

Updates the columns of the specified table with the given values that satisfy the search_condition.

Syntax

UPDATE table_name

SET assignment, assignment, ...

[ WHERE search_condition ]

assignment ::

column = { expr | NULL }

| ( column, column, ... ) = ( expr, expr, ... )

| ( column, column, ... ) = ( query_expression )

Arguments

If the optional WHERE clause is specified, then only rows that satisfy the search_condition are updated. If the WHERE clause is not specified then all rows of the table are updated.

The expressions in the SET clause are evaluated for each row of the table if they are dependent on the columns of the target table.

If a query expression is specified on the right hand side for an assignment, the number of expressions in the first SELECT clause of the query expression must be the same as the number of columns listed on the left hand side of the assignment.

If a query expression is specified on the right hand side for an assignment, the query expression must return one row.

If a table has check constraints and if the columns to be updated are part of a check expression, then the check expression is evaluated. If the result of evaluation is FALSE, the UPDATE statement fails.

If a table has primary/candidate keys and if the columns to be updated are part of the primary/candidate key, a check is made as to whether there exists any corresponding row in the referencing table. If so, the UPDATE operation fails.

Examples

UPDATE orders

SET qty = 12000

WHERE order_no = 1001 ;

UPDATE orders

SET (product) =

(SELECT item_name

FROM items

WHERE item_no = 2401

)

WHERE order_no = 1002 ;

UPDATE orders

SET (amount) = (2000 * 30)

WHERE order_no = 1004 ;

UPDATE orders

SET (product, amount) =

(SELECT item_name, price * 30

FROM items

WHERE item_no = 2401

)

WHERE order_no = 1002 ;

Authorization

The user executing this statement must have:

DBA privilege.
UPDATE privilege on all the specified columns of the target table and SELECT privilege on all the other tables referred to in the statement.

SQL Compliance	SQL-92, ODBC Extended SQL grammar. Extensions: assignments of the form (column, column, ... ) = ( expr, expr, ... )
Environment	Embedded SQL, interactive SQL, ODBC applications
Related Statements	SELECT, OPEN, FETCH, search conditions, query expressions