UPDATE (TSQL Statement)
updates records in the specified table.
Syntax
UPDATE { <table_qname> | table_alias }
SET { column = expression } [ ,...n ]
[ FROM { <table_expression> } [ ,...n ] ]
[ WHERE condition ]
<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name
Arguments
- <table_qname>
- is the qualified or unqualified name of the target table to update.
- table_alias
- alias name specified in the
FROM
clause for the target table to update. - column
- column to update.
=
- assignment operator. Compound assignment operators can also be used, such as
+=
,-=
,*=
, etc. - expression
- the value of expression will replace the current value of column.
FROM
{ <table_expression> } [ ,…n ]- specifies the
FROM
clause of the underlyingSELECT
command, which produces the rows from which the target table will be updated. WHERE
condition;- specifies the
WHERE
clause of the underlyingSELECT
command.
Remarks
The function @@ROWCOUNT
returns the number of records updated.
If a constraint is violated, an error is raised and no record is updated.
Internally, the UPDATE
statement works as follows:
- An underlying
SELECT
statement is created, made of theFROM
andWHERE
clauses of theUPDATE
statement. - If the target table doesn’t appear in the
FROM
clause, it is automatically added as first element in this list of tables. - The columns of this underlying
SELECT
are the expressions of theSET
clause. - This underlying
SELECT
statement is run, and for each record produced, the corresponding record in the target table is updated.
For example:
UPDATE mytable
SET d = t.col2
FROM another_table t
WHERE a = t.col1;
As the target table mytable doesn’t appear in the FROM
clause, RSQL automatically adds it in the list at first position.
UPDATE mytable
SET d = t.col2
FROM mytable, another_table t
WHERE a = t.col1;
The underlying SELECT
statement for this UPDATE
statement is:
SELECT t.col2 AS d
FROM mytable, another_table t
WHERE a = t.col1;
Permissions
The user must have update permission on the target table, and select permission on the referenced tables.
Examples
IF OBJECT_ID ('mytable', 'U') IS NOT NULL
DROP TABLE mytable;
CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL);
GO
INSERT INTO mytable VALUES (11, 'Row #1'), (12, 'Row #2'), (13, 'Row #3');
UPDATE mytable
SET a += 5000,
b = 'Hello ' + UPPER(b)
WHERE a <= 12;
SELECT * FROM mytable;
The result is:
a |b |
-----------+-----------------------------------+
13|Row #3 |
5011|Hello ROW #1 |
5012|Hello ROW #2 |
Examples: Update Table from Another Table
Internally, the UPDATE
statement is in fact a SELECT
statement.
Then, for each row of the result, the corresponding row of the target table is updated.
IF OBJECT_ID ('mytable', 'U') IS NOT NULL
DROP TABLE mytable;
IF OBJECT_ID ('another_table', 'U') IS NOT NULL
DROP TABLE another_table;
CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL, d DATE NULL);
CREATE TABLE another_table (col1 INT NOT NULL, col2 DATE NOT NULL);
GO
INSERT INTO mytable(a, b) VALUES
(11, 'Row #1'), (12, 'Row #2'), (13, 'Row #3');
INSERT INTO another_table VALUES
(11, '20150311'), (12, '20150312'), (12, '20150322'), (13, '20150313'), (14, '20150314');
UPDATE mytable
SET d = t.col2
FROM another_table t
WHERE a = t.col1;
/* same as above, but mytable is explicitly put in the FROM clause
UPDATE mytable
SET d = t.col2
FROM mytable, another_table t
WHERE a = t.col1;
*/
SELECT * FROM mytable;
The result is:
a |b |d |
-----------+-----------------------------------+----------+
11|Row #1 |2015-03-11|
12|Row #2 |2015-03-22| <--- could have been 2015-03-12
13|Row #3 |2015-03-13|
Note that as two records from another_table match the record from mytable for a == 12, the result for this record is undefined.
The value of column d could be 2015-03-12 as well as 2015-03-22.
Examples: with JOIN Syntax
All the syntax for the FROM
and WHERE
clause of the SELECT
statement are allowed (JOIN ... ON
, LEFT JOIN ... ON
, etc).
IF OBJECT_ID ('mytable', 'U') IS NOT NULL
DROP TABLE mytable;
IF OBJECT_ID ('another_table', 'U') IS NOT NULL
DROP TABLE another_table;
CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL, d DATE NULL);
CREATE TABLE another_table (col1 INT NOT NULL, col2 DATE NOT NULL);
GO
INSERT INTO mytable(a, b, d) VALUES
(11, 'Row #1', '00010101'), (12, 'Row #2', '00010101'), (13, 'Row #3', '00010101');
INSERT INTO another_table VALUES
(11, '20150311'), (12, '20150312');
UPDATE mt
SET d = t.col2
FROM mytable mt LEFT JOIN another_table t ON a = t.col1
SELECT * FROM mytable;
The result is:
a |b |d |
-----------+-----------------------------------+----------+
11|Row #1 |2015-03-11|
12|Row #2 |2015-03-12|
13|Row #3 | <NULL>|