Use this statement to modify existing rows in database tables.

Syntax 1

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

table-list : 
table-name [,...]  

table-name : 
[ owner.]table-name [ [ AS ] correlation-name ]
| [ owner.]view-name [ [ AS ] correlation-name ]
| derived-table

derived-table : 
( select-statement ) 
[ AS ] correlation-name [ ( column-name [,... ] ) ]

table-expression : 
A full table expression that can include joins. See FROM clause.

Syntax 2

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

Syntax 3

UPDATE [ owner.]table-name
PUBLICATION publication
{  SUBSCRIBE BY expression
| OLD SUBSCRIBE BY expression NEW SUBSCRIBE BY expression
    }
WHERE search-condition

row-limitation :
  FIRST 
| TOP n [ START AT m ]

set-item :
[ correlation-name.]column-name = { expression | DEFAULT }
| [owner-name.]table-name.column-name = { expression | DEFAULT }
| @variable-name = expression

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| FORCE NO OPTIMIZATION
| option-name = option-value

table-name : 
[ owner.]base-table-name
| temporary-table-name
| derived-table-name
| [ owner.]view-name

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

Parameters

Remarks

Character strings inserted into tables are always stored in the same case as they are entered, regardless of whether the database is case sensitive or not. A CHAR data type column updated with the string Street is always held in the database with an uppercase S and the remainder of the letters lowercase. SELECT statements return the string as Street. If the database is not case sensitive, however, all comparisons make Street the same as street, STREET, and so on. Further, if a single-column primary key already contains an entry Street, an INSERT of street is rejected, as it would make the primary key not unique.

If the new value does not differ from the old value, no change is made to the data. However, BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value.

Syntax 1 of the UPDATE statement modifies values in rows of one or more tables. Syntax 2 and 3 are applicable only to SQL Remote.

Syntax 2 is intended for use with SQL Remote only, in single-row updates of a single table executed by the Message Agent. The VERIFY clause contains a set of values that are expected to be present in the row being updated. If the values do not match, any RESOLVE UPDATE triggers are fired before the UPDATE proceeds. The UPDATE does not fail simply because the VERIFY clause fails to match.

Syntax 3 of the UPDATE statement is used to implement a specific SQL Remote feature, and is to be used inside a BEFORE trigger. It provides a full list of SUBSCRIBE BY values any time the list changes. It is placed in SQL Remote triggers so that the database server can compute the current list of SUBSCRIBE BY values. Both lists are placed in the transaction log.

The Message Agent uses the two lists to make sure that the row moves to any remote database that did not have the row and now needs it. The Message Agent also removes the row from any remote database that has the row and no longer needs it. A remote database that has the row and still needs it is not affected by the UPDATE statement.

For publications created using a subquery in a SUBSCRIBE BY clause, you must write a trigger containing syntax 3 of the UPDATE statement to ensure that the rows are kept in their proper subscriptions.

Syntax 3 of the UPDATE statement allows the old SUBSCRIBE BY list and the new SUBSCRIBE BY list to be explicitly specified, which can make SQL Remote triggers more efficient. In the absence of these lists, the database server computes the old SUBSCRIBE BY list from the publication definition. Since the new SUBSCRIBE BY list is commonly only slightly different from the old SUBSCRIBE BY list, the work to compute the old list may be done twice. By specifying both the old and new lists, you can avoid this extra work.

The SUBSCRIBE BY expression is either a value or a subquery.

Syntax 3 of the UPDATE statement makes an entry in the transaction log, but does not change the database table.

Updating a significant amount of data using the UPDATE statement also updates column statistics.

Permissions

Must have UPDATE permission for the columns being modified.

Side effects

Column statistics are updated.

See also

• DELETE statement
• INSERT statement
• FROM clause
• Joins: Retrieving data from several tables
• UPDATE (positioned) statement [ESQL] [SP]
• Locking during updates

Standards and compatibility

Examples

Using the sample database, this example transfers employee Philip Chin (employee 129) from the sales department to the marketing department.

UPDATE Employees
SET DepartmentID = 400
WHERE EmployeeID = 129;

Using the sample database, this example renumbers all existing sales orders by subtracting 2000 from the ID.

UPDATE SalesOrders AS orders
SET orders.ID = orders.ID - 2000
ORDER BY orders.ID ASC;

This update is possible only if the foreign key of the SalesOrderItems table (referencing the primary key SalesOrders.ID) is defined with the action ON UPDATE CASCADE. The SalesOrderItems table is then updated as well.

For more information about foreign key properties, see ALTER TABLE statement and CREATE TABLE statement.

Using the sample database, this example changes the price of a product at isolation level 2, rather than using the current isolation level setting of the database.

UPDATE Products
SET UnitPrice = 7.00
WHERE ID = 501
OPTION( isolation_level = 2 );

The following example shows how to update a table to set a column to its default value. In this example, you create a table, MyTable, populate it with data, and then execute an UPDATE statement specifying the SET clause to change the column values to their defaults.

CREATE TABLE MyTable(
   PK INT PRIMARY KEY  DEFAULT AUTOINCREMENT,
   TableName CHAR(128) NOT NULL,
   TableNameLen INT DEFAULT 20,
   LastUser CHAR(10) DEFAULT last user,
   LastTime TIMESTAMP DEFAULT TIMESTAMP,
   LastTimestamp TIMESTAMP DEFAULT @@dbts );

INSERT INTO MyTable WITH AUTO NAME
   SELECT
      LENGTH(t.table_name) AS TableNameLen,
      t.table_name AS TableName
   FROM SYS.SYSTAB t
   WHERE table_id<=10;

UPDATE MyTable SET  LastTime = DEFAULT, LastTimestamp = DEFAULT
   WHERE TableName LIKE '%sys%';