Previous Topic

Next Topic

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 ;
UPDATE orders
          SET status='delivered'
          WHERE EXISTS (SELECT status 
                        FROM orders o,customers c
                        WHERE o.cust_id=c.id AND c.name='FairCom');

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