UPDATE, target checks and OUTPUT

Inspect affected rows before changing them.

UnderstandStudy an examplePractise

Understand the concept

UPDATE changes existing rows. Without WHERE, every row is targeted. Preview with the same predicate, define the expected count, then inspect @@ROWCOUNT or OUTPUT. OUTPUT can show deleted and inserted values.

For UPDATE through a JOIN, ensure one source row per target rather than relying on ambiguous multiple matches. Stock and balances also require concurrency design between reading and writing. The examples roll back their changes.

Combining stock validation and decrement in a single conditional UPDATE reduces the read/write race. If no row changes, the application must handle insufficient stock or a missing record explicitly.

WHEN TO USE IT

Use it for controlled data corrections and state changes.

COMMON PITFALL

Omitting WHERE or previewing with a different predicate.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT ProductId, Stock FROM lab.Product WHERE ProductId = 2;

Mouse currently has stock 40.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Try a Mouse stock change in a transaction and roll it back.

Show the solution and explanation

Worked solution

IF DB_NAME() <> N'SqlStudioLab' THROW 50001, 'Lab only.', 1; BEGIN TRANSACTION; UPDATE lab.Product SET Stock = Stock + 5 WHERE ProductId = 2; SELECT Stock FROM lab.Product WHERE ProductId = 2; ROLLBACK;

Stock is 45 inside the transaction and 40 after rollback.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.