Reference Manual
CHAPTER 9. SQL Statements
To modify existing rows in database tables.
UPDATE table-list
... SET column-name[.field-name ] = expression, ...
... [ FROM table-list ]
... [ WHERE search-condition ]
... [ ORDER BY expression [ ASC | DESC ] ,... ]
UPDATE table-list
... SET column-name [.field-name ] = expression, ...
... [ VERIFY ( column-name, ... ) VALUES ( expression, ... ) ]
... [ WHERE search-condition ]
... [ ORDER BY expression [ ASC | DESC ] ,... ]
UPDATE table
...PUBLICATION publication
...{ SUBSCRIBE BY expression
| OLD SUBSCRIBE BY expression NEW SUBSCRIBE BY expression
}
...WHERE search-condition
Must have UPDATE permission for the columns being modified.
None.
The UPDATE statement modifies values in rows of one or more tables. Each named column is set to the value of the expression on the right hand side of the equal sign. There are no restrictions on the expression. Even column-name can be used in the expression—the old value will be used. For a description of the table list and how to do joins, see Joins: Retrieving Data from Several Tables.
The field-name is for use with Java columns, to update the value of a public field in the column.
Syntax 2 and Syntax 3 are applicable only to SQL Remote.
If no WHERE clause is specified, every row is updated. If a WHERE clause is specified, only rows satisfying the search condition are updated.
Normally, the order that rows are updated doesn't matter. However, in conjunction with the NUMBER(*) function, an ordering can be useful to get increasing numbers added to the rows in some specified order. Also, if you wish to do something like add 1 to a set of sequential primary key, it is necessary to do this in descending order by primary key, so that you do not get duplicate primary keys during the operation.
Views can be updated unless the SELECT statement defining the contains a GROUP BY clause or aggregate function, or involves a UNION operation.
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. Thus, a character data type column updated with a string Value is always held in the database with an upper-case V and the remainder of the letters lower case. SELECT statements return the string as Value. If the database is not case-sensitive, however, all comparisons make Value the same as value, VALUE, and so on. Further, if a single-column primary key already contains an entry Value, an INSERT of value is rejected, as it would make the primary key not unique.
The optional FROM clause allows tables to be updated based on joins. If the FROM clause is present, the WHERE clause qualifies the rows of the FROM clause. Data is updated only in the table list immediately following the UPDATE keyword.
If a FROM clause is used, it is important to qualify the table name that is being updated the same way in both parts of the statement. If a correlation name is used in one place, the same correlation name must be used in the other. Otherwise, an error is generated.
For a full description of joins, see Joins: Retrieving Data from Several Tables.
Syntax 2 is intended for use with SQL Remote only, in single-row updates 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 is intended for use with SQL Remote only, inside a BEFORE trigger. The purpose is to provide 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 be affected by the UPDATE statement.
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 is used to implement a specific SQL Remote feature, and is to be used inside a BEFORE trigger.
For publications created using a subquery in a SUBSCRIBE BY clause, you must write a trigger containing syntax 3 of the UPDATE statement in order to ensure that the rows are kept in their proper subscriptions.
Syntax 3 of the UPDATE statement makes an entry in the transaction log, but does not change the database table.
SQL/92 Syntax 1 is an entry-level feature. Syntax 2 and 3 are vendor extensions for use only with SQL Remote.
Sybase Subject to the expressions being compatible, the syntax of the UPDATE statement (syntax 1) is compatible between Adaptive Server Enterprise and Adaptive Server Anywhere. Syntax 2 and 3 are not supported.
Transfer employee Philip Chin (employee 129) from the sales department to the marketing department.
UPDATE employee SET dept_id = 400 WHERE emp_id = 129 ;
Sales orders currently start at ID 2001. Renumber all existing sales orders by subtracting 2000 from the ID.
UPDATE sales_order_items AS items , sales_order AS orders SET items.id = items.id - 2000, orders.id = orders.id - 2000 ;