join-type ::= INNER | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ]
table-subquery ::= query-specification [ [ UNION [ ALL ] query-specification ]... ]
subquery-expression ::= ( query-specification )
Remarks
UPDATE statements, as with DELETE and INSERT, behave in an atomic manner. That is, if an update of more than one row fails, then all updates of previous rows by the same statement are rolled back.
In the SET clause of an UPDATE statement, you may specify a subquery. This feature allows you to update information in a table based on data in another table or another part of the same table.
You may use the keyword DEFAULT to set the value to the default value defined for the given column. If no default value is defined, NULL is used if the column is nullable, and if not, an error is returned. For information about default values and true nulls and legacy nulls from older releases, see SET TRUENULLCREATE.
The UPDATE statement can update only a single table at a time. UPDATE can relate to other tables through a subquery in the SET clause. This can be a correlated subquery that depends in part on the contents of the table being updated, or it can be a noncorrelated subquery that depends only on another table.
For example, here is a correlated subquery:
UPDATE t1 SET t1.c2 = (SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1)
Compared to a noncorrelated subquery:
UPDATE t1 SET t1.c2 = (SELECT SUM(t2.c2) FROM t2 WHERE t2.c1 = 10)
The same logic is used to process pure SELECT statements and subqueries, so the subquery can consist of any valid SELECT statement. Subqueries follow no special rules.
If SELECT within an UPDATE returns no rows, then the UPDATE inserts NULL. If the given columns are not nullable, then the UPDATE fails. If SELECT returns more than one row, then UPDATE fails.
An UPDATE statement does not allow the use of join tables in the statement. Instead, use a correlated subquery in the SET clause like the one shown in the example above.
For information about true nulls and legacy nulls, see SET TRUENULLCREATE.
Updating Data Longer Than the Maximum Literal String
The maximum literal string supported by Zen is 15,000 bytes. You can handle data longer than this using direct SQL statements, breaking the update into multiple calls. Start with a statement like this:
UPDATE table1 SET longfield = '15000 bytes of text' WHERE restriction
Then issue the following statement to add more data:
UPDATE table1 SET longfield = notefield + '15000 more bytes of text' WHERE restriction
FROM Clause
Some confusion may arise pertaining to the optional FROM clause and references to the table being updated (referred to as the “update table”). If the update table occurs in the FROM clause, then one of the occurrences is the same instance of the table being updated.
For example, in the statement UPDATE t1 SET c1 = 1 FROM t1, t2 WHERE t1.c2 = t2.c2, the t1 immediately after UPDATE is the same instance of table t1 as the t1 after FROM. Therefore, the statement is identical to UPDATE t1 SET c1 = 1 FROM t2 WHERE t1.c2 = t2.c2.
If the update table occurs in the FROM clause multiple times, one occurrence must be identified as the same instance as the update table. The FROM clause reference that is identified as the same instance as the update table is the one that does not have a specified alias.
Therefore, the statement UPDATE t1 SET t1.c1 = 1 FROM t1 a, t1 b WHERE a.c2 = b.c2 is invalid because both instances of t1 in the FROM clause contain an alias. The following version is valid: UPDATE t1 SET t1.c1 = 1 FROM t1, t1 b WHERE t1.c2 = b.c2.
The following conditions apply to the FROM clause:
•If the UPDATE statement contains an optional FROM clause, the table reference prior to the FROM clause cannot have an alias specified. For example, UPDATE t1 a SET a.c1 = 1 FROM t2 WHERE a.c2 = t2.c2 returns the following error:
SQL_ERROR (-1)
SQLSTATE of "37000"
"Table alias not allowed in UPDATE/DELETE statement with optional FROM."
A valid version of the statement is UPDATE t1 SET t1.c1 = 1 FROM t2 WHERE t1.c2 = t2.c2 or UPDATE t1 SET t1.c1 = 1 FROM t1 a, t2 WHERE a.c2 = t2.c2.
•If more than one reference to the update table appears in the FROM clause, then only one of the references can have a specified alias. For example, UPDATE t1 SET t1.c1 = 1 FROM t1 a, t1 b WHERE a.c2 = b.c2 returns the following error:
SQL_ERROR (-1)
SQLSTATE of "37000" and
"The table t1 is ambiguous."
In the erroneous statement, assume that you want table t1 with alias “a” to be the same instance of the update table. A valid version of the statement would then be UPDATE t1 SET t1.c1 = 1 FROM t1, t1 b WHERE t1.c2 = b.c2.
•The FROM clause is supported in an UPDATE statement only at the session level. The FROM clause is not supported if the UPDATE statement occurs within a stored procedure.
Examples
The following example updates the record in the faculty table and sets salary as 95000 for ID 103657107.
UPDATE Faculty SET salary = 95000.00 WHERE ID = 103657107
============
The following example shows how to use the DEFAULT keyword.
UPDATE t1 SET c2 = DEFAULT WHERE c2 = 'bcd'
UPDATE t1 SET c1 = DEFAULT, c2 = DEFAULT
============
The following example changes the credit hours for Economics 305 in the course table from 3 to 4.
UPDATE Course SET Credit_Hours = 4 WHERE Name = 'ECO 305'
============
The following example updates the address for a person in the Person table:
UPDATE Person p
SET p.Street = '123 Lamar',
p.zip = '78758',
p.phone = 5123334444
WHERE p.ID = 131542520
============
Subquery Example A
Two tables are created and rows are inserted. The first table, t5, is updated with a column value from the second table, t6, in each row where table t5 has the value 2 for column c1. Because there is more than one row in table t6 containing a value of 3 for column c2, the first UPDATE fails because more than one row is returned by the subquery. This result occurs even though the result value is the same in both cases. As shown in the second UPDATE, using the DISTINCT keyword in the subquery eliminates the duplicate results and allows the statement to succeed.
CREATE TABLE t5 (c1 INT, c2 INT)
CREATE TABLE t6 (c1 INT, c2 INT)
INSERT INTO t5(c1, c2) VALUES (1,3)
INSERT INTO t5(c1, c2) VALUES (2,4)
INSERT INTO t6(c1, c2) VALUES (2,3)
INSERT INTO t6(c1, c2) VALUES (1,2)
INSERT INTO t6(c1, c2) VALUES (3,3)
SELECT * FROM t5
Results:
c1 c2
---------- -----
1 3
2 4
UPDATE t5 SET t5.c1=(SELECT c2 FROM t6 WHERE c2=3) WHERE t5.c1=2 — Note that the query fails
UPDATE t5 SET t5.c1=(SELECT DISTINCT c2 FROM t6 WHERE c2=3) WHERE t5.c1=2 — Note that the query succeeds
SELECT * FROM t5
Results:
c1 c2
---------- -----
1 3
3 4
============
Subquery Example B
Two tables are created and a variety of valid syntax examples are demonstrated. Note the cases where UPDATE fails because the subquery returns more than one row. Also note that UPDATE succeeds and NULL is inserted if the subquery returns no rows (where NULL values are allowed).
CREATE TABLE t1 (c1 INT, c2 INT)
CREATE TABLE t2 (c1 INT, c2 INT)
INSERT INTO t1 VALUES (1, 0)
INSERT INTO t1 VALUES (2, 0)
INSERT INTO t1 VALUES (3, 0)
INSERT INTO t2 VALUES (1, 100)
INSERT INTO t2 VALUES (2, 200)
UPDATE t1 SET t1.c2 = (SELECT SUM(t2.c2) FROM t2)
UPDATE t1 SET t1.c2 = 0
UPDATE t1 SET t1.c2 = (SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1)
UPDATE t1 SET t1.c2 = @@IDENTITY
UPDATE t1 SET t1.c2 = @@ROWCOUNT
UPDATE t1 SET t1.c2 = (SELECT @@IDENTITY)
UPDATE t1 SET t1.c2 = (SELECT @@ROWCOUNT)
UPDATE t1 SET t1.c2 = (SELECT t2.c2 FROM t2) — update fails
INSERT INTO t2 VALUES (1, 150)
INSERT INTO t2 VALUES (2, 250)
UPDATE t1 SET t1.c2 = (SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) — update fails
UPDATE t1 SET t1.c2 = (SELECT t2.c2 FROM t2 WHERE t2.c1 = 5) — Note that the update succeeds, NULL is inserted for all rows of t1.c2
UPDATE t1 SET t1.c2 = (SELECT SUM(t2.c2) FROM t2 WHERE t2.c1 = t1.c1)
============
The following example creates table t1 and t2 and populates them with data. The UPDATE statement uses a FROM clause to specify another table from which to get the new value.
DROP table t1
CREATE table t1 (c1 integer, c2 integer)
INSERT INTO t1 VALUES (0, 10)
INSERT INTO t1 VALUES (0, 10)
INSERT INTO t1 VALUES (2, 20)
INSERT INTO t1 VALUES (2, 20)
DROP table t2
CREATE table t2 (c1 integer, c2 integer)
INSERT INTO t2 VALUES (2, 20)
INSERT INTO t2 VALUES (2, 20)
INSERT INTO t2 VALUES (3, 30)
INSERT INTO t2 VALUES (3, 30)
UPDATE t1 SET t1.c1 = t2.c1 FROM t2 WHERE t1.c2 = t2.c2