A simplified version of the UPDATE statement syntax is:

UPDATE table-name 
SET column_name = expression 
WHERE search-condition

If the company Newton Ent. (in the Customers table of the SQL Anywhere sample database) is taken over by Einstein, Inc., you can update the name of the company using a statement such as the following:

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE CompanyName = 'Newton Ent.';

You can use any expression in the WHERE clause. If you are not sure how the company name was spelled, you could try updating any company called Newton, with a statement such as the following:

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE CompanyName LIKE 'Newton%';

The search condition need not refer to the column being updated. The company ID for Newton Entertainments is 109. As the ID value is the primary key for the table, you could be sure of updating the correct row using the following statement:

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE ID = 109;

The SET clause specifies which columns are to be updated, and what their new values are. The WHERE clause determines the row or rows to be updated. If you do not have a WHERE clause, the specified columns of all rows are updated with the values given in the SET clause.

The expressions specified in a SET clause can be a constant literal, a host or SQL variable, a subquery, a special value such as CURRENT TIMESTAMP, an expression value pulled from another table, or any combination of these. You can also specify DEFAULT in a SET clause to denote the default value for that base table column. If the data type of the expression differs from the data type of the column to be modified, the database server automatically converts the expression to the column's type, if possible. If the conversion is not possible, a data exception results and the UPDATE statement fails.

You can use the SET clause to set the value of a variable, in addition to modifying column values. This example assigns a value to the variable @var in addition to updating table T:

UPDATE T 
SET @var = expression1, col1 = expression2
WHERE...;

This is roughly equivalent to the serial execution of a SELECT statement, followed by an UPDATE:

SELECT @var = expression1
FROM T
WHERE... ;
UPDATE T SET col1 = expression2
WHERE...;

The advantage of variable assignment within an UPDATE statement is that the variable's value can be set within the execution of the statement while write locks are held, which prevents the assignment of unexpected values due to concurrent update activity from other connections.

The WHERE clause specifies which rows are to be updated by applying search-condition to the table or Cartesian product of table expressions specified in the UPDATE statement. For example, the following statement replaces the One Size Fits All Tee Shirt with an Extra Large Tee Shirt

UPDATE Products
SET Size  = 'Extra Large'
WHERE Name = 'Tee Shirt'
   AND Size = 'One Size Fits All';

More complex forms of the UPDATE statement permit updates over joins and other types of table expressions.

As an example, Syntax 1 of the UPDATE statement is:

UPDATE [ row-limitation ] table-name 
SET set-item[, ...]
FROM table-expression [, ...] ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ] , ...]
[ OPTION( query-hint, ... ) ]

The semantics of this form of the UPDATE statement are to first compute a result set consisting of all combinations of rows from each table-expression, subsequently apply the search-condition in the WHERE clause, and then order the resulting rows using the ORDER BY clause. This computation results in the set of rows that will be modified. Each table-expression can consist of joins of base tables, views, and derived tables. The syntax permits the update of one or more tables with values from columns in other tables. The query optimizer may reorder the operations to create a more efficient execution strategy for the UPDATE statement.

If a base table row appears in a set of rows to be modified more than once, then the row is updated multiple times if the row's new values differ with each manipulation attempt. If a BEFORE ROW UPDATE trigger exists, the BEFORE ROW UPDATE trigger is fired for each individual row manipulation, subject to the trigger's UPDATE OF column-list clause. AFTER ROW UPDATE triggers are also fired with each row manipulation, but only if the row's values are actually changed, subject to the trigger's UPDATE OF column-list clause.

Triggers are fired for each updated table based on the type of the trigger and the value of the ORDER clause with each trigger definition. If an UPDATE statement modifies more than one table, however, the order in which the tables are updated is not guaranteed.

The following example creates a BEFORE ROW UPDATE trigger and an AFTER STATEMENT UPDATE trigger on the Products table, each of which prints a message in the database server messages window:

CREATE OR REPLACE TRIGGER trigger0
BEFORE UPDATE 
ON Products
REFERENCING OLD AS old_product NEW AS new_product 
FOR EACH ROW
BEGIN
   PRINT ('BEFORE row: PK value: ' || old_product.ID || ' New Price: ' || new_product.UnitPrice );
END;

CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE 
ON Products
REFERENCING NEW AS new_product 
FOR EACH STATEMENT
BEGIN
  DECLARE @pk INTEGER;
  DECLARE @newUnitPrice DECIMAL(12,2);
  DECLARE @err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
  DECLARE new_curs CURSOR FOR
   SELECT ID, UnitPrice FROM new_product;
  OPEN new_curs;
  LoopGetRow:
    LOOP
      FETCH NEXT new_curs INTO @pk, @newUnitPrice;
      IF SQLSTATE = @err_notfound THEN 
         LEAVE LoopGetRow
      END IF;
      PRINT ('AFTER stmt: PK value: ' || @pk || ' Unit price: ' || @newUnitPrice );
  END LOOP LoopGetRow;
  CLOSE new_curs
END;

Suppose you then execute an UPDATE statement over a join of the Products table with the SalesOrderItems table, to discount by 5% those products that have shipped since April 1, 2001 and that have at least one large order:

UPDATE Products p JOIN SalesOrderItems s ON (p.ID = s.ProductID)
SET p.UnitPrice = p.UnitPrice * 0.95
WHERE s.ShipDate > '2001-04-01' AND s.Quantity >= 72;

The database server messages window displays the following messages:

BEFORE row: PK value: 700 New Price: 14.25
BEFORE row: PK value: 302 New Price: 13.30
BEFORE row: PK value: 700 New Price: 13.54
AFTER stmt: PK value: 700 Unit price: 14.25
AFTER stmt: PK value: 302 Unit price: 13.30
AFTER stmt: PK value: 700 Unit price: 13.54

The messages indicate that Product 700 was updated twice, as Product 700 was included in two different orders that matched the search condition in the UPDATE statement. The duplicate updates are visible to both the BEFORE ROW trigger and the AFTER STATEMENT trigger. With each row manipulation, the old and new values for each trigger invocation are changed accordingly. With AFTER STATEMENT triggers, the order of the rows in the temporary tables formed by the REFERENCING clause may not match the order of the rows were modified and the precise order of those rows is not guaranteed.

Because of the duplicate updates, Product 700's UnitPrice was discounted twice, lowering it from $15.00 initially to $13.54 (yielding a 9.75% discount), rather than only $14.25. To avoid this unintended consequence, you could instead formulate the UPDATE statement to use an EXISTS subquery, rather than a join, to guarantee that each Product row is modified at most once. The rewritten UPDATE statement uses both an EXISTS subquery and the alternate UPDATE statement syntax that permits a FROM clause:

UPDATE Products AS p 
SET p.UnitPrice = p.UnitPrice * 0.95
FROM Products AS p
WHERE EXISTS( 
    SELECT * 
    FROM SalesOrderItems s 
    WHERE p.ID = s.ProductID
       AND s.ShipDate > '2001-04-01' 
       AND s.Quantity >= 72);

If an UPDATE statement violates a referential integrity constraint during execution, the statement's behavior is controlled by the setting of the wait_for_commit option. If the wait_for_commit option is set to Off, and a referential constraint violation occurs, the effects of the UPDATE statement are immediately automatically rolled back and an error message appears. If the wait_for_commit option is set to On, any referential integrity constraint violation caused by the UPDATE statement is temporarily ignored, to be checked when the connection performs a COMMIT.

If the base table or tables being modified have primary keys, UNIQUE constraints, or unique indexes, then row-by-row execution of the UPDATE statement may lead to a uniqueness constraint violation. For example, you may issue an UPDATE statement that increments all of the primary key column values for a table T:

UPDATE T SET PKcol = PKcol + 1;

When a uniqueness violation occurs during the execution of an UPDATE statement, the database server automatically:

During the execution of the UPDATE statement, which rows are updated successfully and which rows are temporarily deleted depends on the order of evaluation and cannot be guaranteed. The behavior of SQL requests from other connections executing at weaker isolation levels (isolation levels 0, 1, or 2) may be affected by these temporarily deleted rows. Any BEFORE or AFTER ROW triggers of the modified table are passed each row's old and new values as per the trigger's REFERENCING clause, but if the ROW trigger issues a separate SQL statement against the modified table, rows that are held in the temporary table will be missing.

After the UPDATE statement has completed modifying each row, the rows held in the temporary table are then inserted back into the base table. If a uniqueness violation still occurs, then the entire UPDATE statement is rolled back. Only when all of the rows held in the temporary table have been successfully re-inserted into the base table are any AFTER STATEMENT triggers fired.

The database server does not use a hold table to store rows temporarily if the base table being modified is the target of a referential integrity constraint action, including ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE DEFAULT.

• UPDATE statement
• Result sets in Interactive SQL
• ansi_update_constraints option
• Integrity checks on DELETE or UPDATE
• Locks during updates