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 underlying SELECT command, which produces the rows from which the target table will be updated.

WHERE condition;

specifies the WHERE clause of the underlying SELECT 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 the FROM and WHERE clauses of the UPDATE 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 the SET 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>|