UPDATE¶
Updates specified rows in the target table with new values.
Syntax¶
Required parameters¶
target_tableSpecifies the table to update.
col_nameSpecifies the name of a column in
target_table. Do not include the table name. For example,UPDATE t1 SET t1.col = 1is invalid.valueSpecifies the new value to set in
col_name.
Optional parameters¶
FROM additional_tablesSpecifies one or more tables to use for selecting rows to update or for setting new values. Note that repeating the target table results in a self-join.
WHERE conditionExpression that specifies the rows in the target table to update.
Default: No value (all rows of the target table are updated)
Usage notes¶
-
When a FROM clause contains a JOIN between tables (e.g.
t1andt2), a target row int1may join against (i.e. match) more than one row in tablet2. When this occurs, the target row is called a multi-joined row. When updating a multi-joined row, the ERROR_ON_NONDETERMINISTIC_UPDATE session parameter controls the outcome of the update:- If
FALSE(default value), no error is returned and one of the joined rows is used to update the target row; however, the selected joined row is nondeterministic. - IF
TRUE, an error is returned, including an example of the values of a target row that joins multiple rows.
To set the parameter:
- If
-
If error logging is turned on for the target table, a supported error, such as a NOT NULL or CHECK constraint violation, doesn’t cause the statement to fail. Instead, the statement records the error in the table’s error table, leaves the row that caused the error unchanged, and updates the other rows. For more information, see Error logging for DML statements.
Examples¶
Create a table and insert some rows:
Update a single column for the rows that match a condition:
Update several columns at once, using an expression that refers to the current value:
Update every row in the table (no WHERE clause):
Perform a standard update using two tables:
Update with join that produces nondeterministic results:
With ERROR_ON_NONDETERMINISTIC_UPDATE = FALSE, the statement randomly updates the single row in
targetusing values from one of the following rows insrc:(0, 11),(0, 12),(0,13)With ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE, an error is returned reporting a duplicate DML row
[0, 10].
To avoid this nondeterministic behavior and error, use a 1-to-1 join:
This statement results in the single row in
targetupdated to(0, 11)(values from the row with the minimum value forvinsrc) and will never result in an error.