SQL Server / Basic / Lesson 6

Safe INSERT, UPDATE, and DELETE

Apply deliberate changes to synthetic data, inspect affected rows, and separate previews from atomic correctness. Learn OUTPUT, row-count checks, and identity retrieval.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Use explicit column lists for inserts.
Predict mutation scope and inspect OUTPUT.
Explain atomic conditional updates and transaction boundaries.

Specify the intended state transition

A mutation should describe an allowed transition, not merely a destination value. “Reduce product 7 stock by two if at least two remain” is clearer than “set stock to three.” Define the target key, eligibility condition, expected affected count, and failure meaning. Read the full statement before execution; UPDATE or DELETE without WHERE affects every qualifying row in its target.

A SELECT preview helps humans review a predicate but does not reserve those rows. Another session can change data between the preview and modification. Correctness must live in the modifying statement, its constraints, and any required transaction or concurrency control. Never treat a screenshot of a preview as proof that a later update affected the same set.

Insert explicit columns and observe values

List destination columns in INSERT, even when the current table order seems obvious. This makes intent reviewable and avoids coupling to column position. DEFAULT applies when a value is omitted or DEFAULT is requested; explicit NULL still means NULL. An IDENTITY column normally generates its own value, and a primary key or unique constraint provides the uniqueness guarantee.

OUTPUT exposes affected-row values through inserted and deleted pseudo-tables. INSERT has inserted values, DELETE has deleted values, and UPDATE can expose both. The example creates only a table variable and predicts two rows, but OUTPUT ordering is not guaranteed. More importantly, output can be returned even when a statement later encounters an error and is rolled back; success requires checking completion and transaction outcome.

sql Observe generated identifiers

DECLARE @t TABLE (Id int IDENTITY(1,1) PRIMARY KEY, Name nvarchar(30));
INSERT @t(Name)
OUTPUT inserted.Id,inserted.Name
VALUES (N'Aya'),(N'Omar');
SELECT Id,Name FROM @t ORDER BY Id;

Expected output

Final SELECT:
Id Name
1  Aya
2  Omar

The final SELECT has explicit order. OUTPUT reports affected values but its own row ordering is unspecified.

Put eligibility in the update

An arithmetic update reads and writes the target value in one statement. Stock = Stock - @qty with Stock >= @qty avoids the obvious read-then-write race of calculating a new value in application memory. Check @@ROWCOUNT immediately, because later statements can replace it. Zero affected rows can mean a missing product or insufficient stock; decide whether the API distinguishes those cases.

The example starts with five units and requests two. The predicate permits the transition and OUTPUT shows five becoming three. Validate that quantity is positive before using this pattern; otherwise subtracting a negative quantity increases stock. A CHECK constraint supplies another defense against negative stored stock. Concurrent correctness still needs the full transaction design when the stock change belongs with an order insert.

sql A guarded stock decrement

DECLARE @Stock TABLE (ProductId int PRIMARY KEY, Quantity int CHECK (Quantity>=0));
INSERT @Stock VALUES (7,5);
DECLARE @qty int=2;
UPDATE @Stock SET Quantity=Quantity-@qty
OUTPUT deleted.Quantity AS before_qty,inserted.Quantity AS after_qty
WHERE ProductId=7 AND Quantity>=@qty AND @qty>0;
DECLARE @changed int=@@ROWCOUNT;
SELECT @changed AS changed;

Expected output

before_qty after_qty
5          3

changed
1

The row count is captured before another statement changes it. A request for six would affect zero rows.

Treat deletion and batching as design choices

DELETE removes rows subject to constraints and triggers; TRUNCATE has different restrictions and semantics and is not a convenient substitute for a filtered delete. Foreign keys can reject deletion of referenced parents. Cascading deletion is an explicit schema policy with potentially broad effects. Review retention requirements, audit needs, and recovery options before designing a permanent removal process.

DELETE TOP (100) does not identify the oldest hundred rows by itself. For ordered batches, select keys with TOP and ORDER BY in a subquery or CTE, then delete those keys under an appropriate transaction design. Repeated batches need a progress condition. Otherwise the process may revisit rows or affect a changing eligibility set in surprising ways.

Verify a complete outcome

For a single generated identity, SCOPE_IDENTITY returns the last identity in the current scope; @@IDENTITY can be affected by another scope such as a trigger. For multirow inserts, prefer OUTPUT to obtain the set of generated values. Neither identity magnitude nor gaps prove how many rows committed. Track success through the command and transaction protocol.

A complete change review explains the before-state, expected after-state, constraints, affected count, and response to failure. Small synthetic examples make these obligations visible. In later lessons, combine mutations inside TRY/CATCH with an explicit transaction. For now, distinguish statement success, transaction commitment, and the application receiving confirmation: they are separate events with different failure modes.

Common errors and fixes

Every row changed.

Why it happens: The mutation omitted or weakened its WHERE predicate.
How to fix it: Restore through an approved recovery process; correct and review the predicate in training.
Prevent it next time: Require target keys and expected affected counts.

The DELETE statement conflicted with the REFERENCE constraint.

Why it happens: Child rows still reference the parent.
How to fix it: Apply the documented retention policy and valid dependency order.
Prevent it next time: Model deletion policy alongside foreign keys.

Stock increased for a sale.

Why it happens: A negative requested quantity was subtracted.
How to fix it: Reject nonpositive quantities and retain database constraints.
Prevent it next time: Test zero, negative, excessive, and normal requests.

Check your understanding

1. Starting stock is 5. Two clients each want 4. Why is an application read followed by SET Stock=1 unsafe?

Show worked solution

Both can read 5 and both write 1, accepting eight units of sales. A guarded arithmetic update permits one decrement; the later request sees insufficient remaining stock and affects zero rows under normal locking semantics.

2. A trigger inserts an audit identity after a customer insert. Which identity retrieval strategy is appropriate?