|
|
|
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.
Expressions other than the query expressions mentioned above can be scalar subqueries. A scalar sub-query returns one value. The expressions and search conditions can also contain scalar sub-queries. Refer to Scalar Sub-query Expressions for more information on the scalar sub-queries.
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.
IDENTITY columns cannot be updated and hence cannot be specified as one of the column names for assignment.
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 ;
UPDATE orders
SET status='delivered'
WHERE EXISTS (SELECT status
FROM orders o,customers c
WHERE o.cust_id=c.id AND c.name='FairCom');
UPDATE orders
SET product = (
SELECT item_name
FROM items
WHERE item_no = 2401),
amount = (
SELECT price * 30
FROM items
WHERE item_no = 2401)
WHERE order_no = 1002 ;
UPDATE item
SET stock = stock + (
SELECT SUM(order_item.quantity)
FROM order_item
WHERE order_item.order_no = 341
AND order_item.item_no = item.item_no)
WHERE item_no = (
SELECT MAX(item_no)
FROM order_item
WHERE order_no = 341);
Authorization
The user executing this statement must have:
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 |
