.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 13 October 2026
An UPDATE overwrites the old values and carries the evidence off with them. So write it as a SELECT first. Same FROM, same WHERE, and swap in the UPDATE once the rows coming back are the rows you meant. On a statement you are sure about that costs ten seconds. On the one you should never have run, it saves your afternoon.
Every query below runs against the same three tables. The other T-SQL tutorials on this site reuse them, so the examples stack up as you go and no article starts from scratch.
CREATE TABLE dbo.customers (
customer_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
company_name NVARCHAR(200) NULL,
country_code CHAR(2) NOT NULL,
created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE TABLE dbo.orders (
order_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES dbo.customers(customer_id),
order_status VARCHAR(20) NOT NULL DEFAULT 'pending',
order_total DECIMAL(10,2) NOT NULL,
discount_pct DECIMAL(5,2) NULL,
placed_at DATETIME2(3) NOT NULL,
shipped_at DATETIME2(3) NULL
);
CREATE TABLE dbo.order_items (
order_item_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
order_id INT NOT NULL REFERENCES dbo.orders(order_id),
sku VARCHAR(40) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL
);A basic UPDATE MSSQL query with a WHERE clause
Three lines. One of them decides your afternoon.
UPDATE dbo.orders
SET order_status = 'shipped',
shipped_at = SYSUTCDATETIME()
WHERE order_id = 4821;Multiple columns go in one SET list, separated by commas. Every assignment on the right side sees the pre-update value of the row, so SET a = b, b = a swaps two columns correctly in a single statement. That surprises people arriving from procedural languages.
Before running that, run this:
SELECT order_id, order_status, shipped_at
FROM dbo.orders
WHERE order_id = 4821;Same table, same predicate, no damage. You get the row count and the current values, and a wrong predicate costs you nothing at this point. Getting predicates right is most of the job, and every operator in a SQL Server WHERE clause has its own way of misfiring. I do this even for single-row updates by primary key. The cost is two seconds. The failure mode is a restore.
An UPDATE with no WHERE clause updates every row. SQL Server will not warn you or cap it at some sensible number. SSMS has no equivalent of the MySQL client's safe-updates mode, so the guard rail is your own habit.
The fixed clause order: SET, OUTPUT, FROM, WHERE
T-SQL's UPDATE grammar is rigid. Clause order is what people arriving from another engine get wrong first, and it is the same for every UPDATE MSSQL query no matter how many tables are involved:
UPDATE [ TOP ( expression ) [ PERCENT ] ] table_or_alias
SET column_name = { expression | DEFAULT | NULL } [ ,...n ]
[ <OUTPUT Clause> ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ OPTION ( <query_hint> [ ,...n ] ) ];SET comes first, always. MySQL lets you write the join before SET (UPDATE orders o JOIN customers c ON ... SET ...) and that muscle memory produces this in SQL Server:
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'FROM'.Same message for OUTPUT placed after FROM, or WHERE placed before it. The parser matches a fixed clause sequence and makes no attempt to work out what you meant. Microsoft's UPDATE reference prints that sequence at the top of the page, and reading it once is enough for the shape to stick. Memorise SET, OUTPUT, FROM, WHERE and the syntax errors stop.
UPDATE with JOIN using the FROM clause
To update one table using values from another, name the alias on the UPDATE line and define that alias in FROM:
UPDATE o
SET o.order_status = 'flagged'
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
WHERE c.country_code = 'IR'
AND o.order_status = 'pending';Only dbo.orders is modified. The customers table is read-only here, which is the whole point of the FROM extension: filter and pull values from anywhere, write to exactly one table.
Name the table as UPDATE dbo.orders on the first line while also aliasing it as o in the FROM clause. SQL Server reads that as two separate references to dbo.orders. Then it complains about duplicate exposed names. Alias it once and target the alias.
Non-deterministic joins give undefined results with no error
This is the behaviour I would change if I got one wish. It is also why an UPDATE MSSQL command that reads from a table I did not build gets checked before it runs. Microsoft's own wording: "The results of an UPDATE statement are undefined if the statement includes a FROM clause that isn't specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement isn't deterministic."
Read that again with the emphasis on undefined. There's no error and no warning. Oracle refuses the same statement with ORA-30926 when two source rows match one target row. PostgreSQL's UPDATE ... FROM has SQL Server's gap, and its documentation says which join row gets used "is not readily predictable". SQL Server picks one of them and moves on.
A price feed lands twice in the same day:
CREATE TABLE dbo.price_feed (
sku VARCHAR(40) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
feed_at DATETIME2(3) NOT NULL
);
INSERT INTO dbo.price_feed (sku, unit_price, feed_at)
VALUES ('LD-RAM-32', 129.00, '2026-08-25T09:00:00'),
('LD-RAM-32', 139.00, '2026-08-25T17:30:00');Then the obvious sync:
UPDATE oi
SET oi.unit_price = pf.unit_price
FROM dbo.order_items AS oi
JOIN dbo.price_feed AS pf ON pf.sku = oi.sku;(37 rows affected)Thirty-seven rows, zero errors, and every one of them now holds either 129.00 or 139.00 depending on which row the plan happened to read last. Change the plan (add an index, restore onto a machine with a different core count, let the statistics age) and the same statement produces the other answer. It is repeatable enough to pass your test and unstable enough to fail in production six weeks later.
Check the join key for duplicates before you run
The detection query takes five seconds and I run it on any source table I didn't build myself:
SELECT sku, COUNT(*) AS row_count
FROM dbo.price_feed
GROUP BY sku
HAVING COUNT(*) > 1;sku row_count
----------- ---------
LD-RAM-32 2One row back means the join is non-deterministic. Zero rows back means the key is unique in the source and the UPDATE has exactly one candidate per target row. Decide which duplicate should win and make that explicit:
WITH latest_price AS (
SELECT sku,
unit_price,
ROW_NUMBER() OVER (PARTITION BY sku ORDER BY feed_at DESC) AS rn
FROM dbo.price_feed
)
UPDATE oi
SET oi.unit_price = lp.unit_price
FROM dbo.order_items AS oi
JOIN latest_price AS lp
ON lp.sku = oi.sku
AND lp.rn = 1;Now 139.00 wins because you picked it. The cost is four extra lines.
Compound assignment operators
T-SQL supports +=, -=, *=, /=, %=, &=, ^= and |= in a SET list:
UPDATE dbo.orders
SET order_total += 4.99
WHERE order_id = 4821;That is the same thing as SET order_total = order_total + 4.99. On character data += concatenates. Every operator in that list takes one expression on its right. A compound assignment can sit in the same SET list as ordinary assignments, in any order. Each target row is written once, with one value.
The NULL rule still applies. Wrapping the column is the fix, and the options for doing that are ISNULL, COALESCE and CASE. SET discount_pct += 5 on a row where discount_pct is NULL leaves it NULL, since NULL plus anything is NULL. The same holds for every compound operator above.
UPDATE TOP and why it needs a subquery
UPDATE accepts TOP, and the syntax needs the parentheses:
UPDATE TOP (100) dbo.orders
SET order_status = 'archived'
WHERE order_status = 'closed';Which hundred rows? Unspecified. Microsoft is blunt about it: "The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE aren't arranged in any order." There's nowhere in that syntax to put an ORDER BY, so if you need the oldest hundred and not an arbitrary hundred, pick them in a subquery and join back:
UPDATE o
SET o.order_status = 'archived'
FROM dbo.orders AS o
JOIN (
SELECT TOP (100) order_id
FROM dbo.orders
WHERE order_status = 'closed'
ORDER BY placed_at
) AS batch ON batch.order_id = o.order_id;For a plain "process rows in chunks until they're gone" loop, bare UPDATE TOP (n) is fine and the arbitrary order costs nothing. Order it when the choice of rows is visible to a human.
PERCENT is in the grammar as well. This paragraph is thinner than it should be because PERCENT on an UPDATE turns up about once every few years, and I can't tell you how it behaves on a table big enough to care.
UPDATE with OUTPUT: inserted and deleted
The OUTPUT clause hands you the rows the statement touched, with deleted. holding the pre-update values and inserted. the post-update values. Same pseudo-tables a trigger sees.
UPDATE dbo.orders
SET order_status = 'refunded'
OUTPUT deleted.order_id,
deleted.order_status AS old_status,
inserted.order_status AS new_status,
SYSUTCDATETIME() AS changed_at
WHERE order_id = 4821;One statement, one receipt. Timestamps in these examples come from SYSUTCDATETIME() and not GETDATE(), a preference SQL Server date functions makes the case for at length. The receipt is what turns a risky UPDATE into something reviewable afterwards, and it costs one clause.
OUTPUT works the same way on INSERT and DELETE.
OUTPUT INTO an audit table
Send the receipt somewhere permanent by adding INTO:
CREATE TABLE dbo.order_status_audit (
order_id INT NOT NULL,
old_status VARCHAR(20) NOT NULL,
new_status VARCHAR(20) NOT NULL,
changed_at DATETIME2(3) NOT NULL,
changed_by SYSNAME NOT NULL
);
UPDATE dbo.orders
SET order_status = 'refunded'
OUTPUT deleted.order_id,
deleted.order_status,
inserted.order_status,
SYSUTCDATETIME(),
SUSER_SNAME()
INTO dbo.order_status_audit (order_id, old_status, new_status, changed_at, changed_by)
WHERE order_status = 'disputed'
AND placed_at < '20260101';Note where INTO sits: still in the OUTPUT slot, before FROM and WHERE. Every restriction on it is listed in the OUTPUT clause reference. The ones that come up:
- OUTPUT without INTO fails outright if the target table has enabled triggers for that action.
- The INTO target can't have triggers, can't be on either side of a foreign key, can't carry CHECK constraints and can't have enabled rules. An audit table with no constraints on it, in other words.
- Row order into the output table is not guaranteed, so add your own ordering column if order matters.
- OUTPUT to the client or to a table variable forces a serial plan, which is measurable on large statements.
- Rows are returned to the client even when the statement later errors and rolls back, so a returned row is not proof of a committed row.
OUTPUT is also unavailable against remote tables and views, which is the usual reason it fails on a linked-server statement that works fine locally.
UPDATE from a CTE or a derived table
A common table expression can be the target of the UPDATE as long as it resolves to one updatable base table:
WITH order_totals AS (
SELECT order_id,
SUM(quantity * unit_price) AS calculated_total
FROM dbo.order_items
GROUP BY order_id
)
UPDATE o
SET o.order_total = ot.calculated_total
FROM dbo.orders AS o
JOIN order_totals AS ot ON ot.order_id = o.order_id
WHERE o.order_total <> ot.calculated_total;The GROUP BY makes that join deterministic by construction, which is why I aggregate in a CTE first. Join straight to a detail table and you lose that guarantee. One row per order_id, guaranteed by the grammar.
Two things about CTEs here. Common table expressions have an article of their own and it covers the second one properly. A statement before a WITH needs a terminating semicolon, which is where the defensive ;WITH style comes from. And a CTE isn't a temp table: reference it twice and the query underneath runs twice.
The derived-table form does the same job inline:
UPDATE o
SET o.order_total = ot.calculated_total
FROM dbo.orders AS o
JOIN (
SELECT order_id, SUM(quantity * unit_price) AS calculated_total
FROM dbo.order_items
GROUP BY order_id
) AS ot ON ot.order_id = o.order_id
WHERE o.order_total <> ot.calculated_total;Identical plan. The nesting is what makes the second form hard to read once a query has any depth to it, so I take the CTE.
