Modifies existing rows in database tables.
UPDATE [ row-limitation ] table-expression [, ...] SET set-item[, ...] [ WHERE search-condition ] [ ORDER BY expression [ ASC | DESC ] , ...] [ OPTION( query-hint, ... ) ]
table-expression : A table expression that can include joins, outer joins, views and derived tables. See FROM clause.
UPDATE [ row-limitation ] table-name SET set-item[, ...] FROM table-expression [, ...] ] [ WHERE search-condition ] [ ORDER BY expression [ ASC | DESC ] , ...] [ OPTION( query-hint, ... ) ]
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 [, ...] ) ]
UPDATE table-name SET set-item, ... VERIFY ( column-name, ... ) VALUES ( expression, ... ) [ WHERE search-condition ] [ ORDER BY expression [ ASC | DESC ], ...] [ OPTION( query-hint, ... ) ]
UPDATE [ owner.]table-name PUBLICATION publication { SUBSCRIBE BY expression | OLD SUBSCRIBE BY expression NEW SUBSCRIBE BY expression } WHERE search-condition
row-limitation : FIRST | TOP { ALL | limit-expression } [ START AT startat-expression ] limit-expression : simple-expression startat-expression : simple-expression simple-expression : integer | variable | ( simple-expression ) | ( simple-expression { + | - | * } simple-expression )
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 | number
UPDATE clause For Syntax 1 and Syntax 2, table-expression can include temporary tables, global temporary tables, derived tables, or views. Views and derived tables can be updated unless they are non-updatable. For Syntax 1, a list of more than one table-expression results in a Cartesian product of the rows formed by the underlying table expressions, which can then be restricted via the use of the WHERE clause. Both Syntax 1 and Syntax 2 permit the updating of joins. For Syntax 3 and Syntax 4, table-name must be a base table.
UPDATES can be performed on views only if the query specification defining the view is updatable.
row-limitation clause The row limitation clause allows you to restrict the rows being updated to only a subset of the rows that satisfy the WHERE clause. The TOP and START AT arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The TOP argument must evaluate to a value greater than or equal to 0. The START AT argument must evaluate to a value greater than 0. An ORDER BY clause should be used to order the rows in a meaningful manner.
SET clause The set clause specifies the columns and how the values are changed.
You can use the SET clause to set the column to a computed column value using this format:
SET column-name = expression, ... |
Each named column is set to the value of the expression on the right side of the equal sign. There are no restrictions on the expression. If the expression is a column-name, the old value from that column is used.
If a column has a default defined, you can use the SET clause to set a column to its default value. See the Examples section for an example of this usage.
You can also use the SET clause to assign a variable using the following format:
SET @variable-name = expression, ... |
When assigning a value to a variable, the variable must already be declared, and its name must begin with the at sign (@). If the variable name matches the name of a column in the table to be updated, the UPDATE statement updates the column value and leaves the variable unchanged. Variable and column assignments can be mixed together in any order.
FROM clause The FROM table-expression clause allows tables to be updated based on joins. table-expression can contain arbitrary complex table expressions, such as OUTER, CROSS, and NATURAL joins.
If the FROM clause is present, table-name must specify the sole table to be updated, and it must qualify the name in the same way as it appears in the FROM clause. If correlation names are used in the FROM clause, the identical correlation name must be specified as table-name. If the table expression to be updated is a derived table, the derived table must be repeated in the table-name specification.
Syntax 2 cannot be used if the ansi_update_constraints option is set to Strict.
If a FROM clause is specified, the SET clause may specify only columns from table-name to be updated. Otherwise, an error is generated.
The following statement illustrates a potential ambiguity in table names in UPDATE statements using Syntax 2 that contain table expressions which use correlation names:
UPDATE table_1 SET column_1 = ... FROM table_1 AS alias_1, table_1 AS alias_2 WHERE ... |
Here, each instance of table_1 in the FROM clause has a correlation name, denoting a self-join of table_1 to itself. However, the UPDATE statement fails to specify which of the rows that make up the self-join are to be updated. This can be corrected by specifying the correlation name in the UPDATE statement as follows:
UPDATE table_1 as alias_1 SET column_1 = ... FROM table_1 AS alias_1, table_1 AS alias_2 WHERE ... |
WHERE clause If a WHERE clause is specified, only rows satisfying the search condition are updated. If no WHERE clause is specified, every row is updated.
ORDER BY clause Normally, the order in which rows are updated does not matter. However, in conjunction with the FIRST or TOP clause the order can be significant.
You cannot use ordinal column numbers in the ORDER BY clause.
In order to use the ORDER BY clause, the ansi_update_constraints option must not be set to Strict.
In order to update columns that appear in the ORDER BY clause, the ansi_update_constraints option must be set to Off.
OPTION clause Use this clause to specify hints for executing the statement. The following hints are supported:
OPTION( isolation_level = ... )
specification in the query text overrides all other means of specifying isolation level for a query.
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 UPDATE of another row's primary key to 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 3 and 4 are applicable only to SQL Remote.
Syntax 3 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 4 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 4 of the UPDATE statement to ensure that the rows are kept in their proper subscriptions.
Syntax 4 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 4 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.
Must have UPDATE permission for the columns being modified.
Column statistics are updated to reflect the modified values.
If a table has a primary key, a UNIQUE constraint, or a UNIQUE index, the processing of the UPDATE statement may involve the use of a temporary table if the table manipulations cannot be performed without violating the uniqueness constraint. The temporary table stores rows modified by the UPDATE statement that violate one or more uniqueness constraints. These rows are temporarily deleted from the base table during the execution of the UPDATE statement, and are subsequently re-inserted. This behavior may have implications for AFTER triggers and other concurrent connections.
SQL/2008 Syntax 1 of the UPDATE statement is a core feature of the SQL/2008 standard. Syntax 2 is a vendor extension. Syntax 3 and 4 are vendor extensions for use only with SQL Remote.
Syntax 1 includes support for two optional SQL language features:
Support for updating a join, possibly including one or more derived tables, comprises part of optional SQL language feature T111, "Updatable joins, unions, and columns".
Support for modifying a table referenced in a nested subquery that forms part of the search condition for the UPDATE statement comprises optional SQL/2008 language feature F781, "Self-referencing operations".
The following features of Syntax 1 are vendor extensions:
The FROM and ORDER BY clauses.
The row-limitation clause.
The ability to specify more than one table-expression.
The ability to update a variable using the SET clause.
The OPTION clause.
With Syntax 1, the setting of the ansi_update_constraints option controls which forms of table expressions can be modified. To enforce SQL/2008 core feature compatibility, ensure that the ansi_update_constraints option is set to Strict.
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. Because the statement specifies an ORDER BY clause, and the ordering attribute is also specified in the SET clause, the ansi_update_constraints option must be set to Off or an error is returned.
Using the sample database, this third 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 ); |
Again using the sample database, this fourth example uses Syntax 2 to reset the quantity-on-hand of those Tee Shirts where there exists at least one order whose quantity exceeds the current quantity-on-hand:
UPDATE Products AS a SET Quantity = 0 FROM Products a JOIN SalesOrderItems b ON a.ID = b.ProductID WHERE a.Name = 'Tee Shirt' AND b.Quantity > a.Quantity; |
In this fifth example, Syntax 1 is used to both reset the quantity-on-hand for those Tee Shirts, and reset the ShipDate for the Tee Shirt order to today's date:
UPDATE Products a JOIN SalesOrderItems b on a.ID = b.ProductID SET a.Quantity = 0, b.ShipDate = CAST( NOW() AS DATE) WHERE a.Name = 'Tee Shirt' AND b.Quantity > a.Quantity |
Examples four and five require the ansi_update_constraints option to be set to a value other than Strict.
This sixth 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%'; |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |