Update SQL Server from staging without nondeterministic joins

SQL Server supports updating a target from a joined source, but syntax is the easy part. The production risk is cardinality: if more than one source row matches one target row, Microsoft documents the result as undefined. A plausible-looking statement can therefore choose an arbitrary source value.

Treat staging as an input contract

Assume dbo.CustomerStage should contain at most one row per CustomerId. Prove that before updating:

SELECT CustomerId, COUNT_BIG(*) AS SourceRows
FROM dbo.CustomerStage
GROUP BY CustomerId
HAVING COUNT_BIG(*) > 1;

Any returned row blocks the operation. Do not hide duplicates with DISTINCT unless every projected value is identical and that equivalence is part of a documented ingestion contract. Do not choose an arbitrary row with TOP (1). If “latest wins” is valid, define a total ordering with a stable tie-breaker, materialize the winner set, and verify it is unique.

Also validate nullability, types, allowed status values, referential constraints, and the intended batch identifier. A permanent staging table should have a uniqueness constraint such as UNIQUE (BatchId, CustomerId) so bad cardinality is rejected at ingestion rather than discovered during a live update.

Preview the exact change set

SELECT
    t.CustomerId,
    t.Status AS OldStatus,
    s.Status AS NewStatus
FROM dbo.Customer AS t
JOIN dbo.CustomerStage AS s
  ON s.CustomerId = t.CustomerId
WHERE s.BatchId = @BatchId
  AND (t.Status <> s.Status
       OR (t.Status IS NULL AND s.Status IS NOT NULL)
       OR (t.Status IS NOT NULL AND s.Status IS NULL));

Explicit null handling matters because NULL <> value is unknown, not true. Compare the preview count with an approved bound and sample critical rows. Ensure the predicate scopes one immutable batch; a concurrently changing staging set makes preview and update disagree.

The deterministic joined update

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE t
SET
    t.Status = s.Status,
    t.ModifiedAt = SYSUTCDATETIME()
OUTPUT
    deleted.CustomerId,
    deleted.Status,
    inserted.Status
INTO dbo.CustomerUpdateAudit (CustomerId, OldStatus, NewStatus)
FROM dbo.Customer AS t
JOIN dbo.CustomerStage AS s
  ON s.CustomerId = t.CustomerId
WHERE s.BatchId = @BatchId
  AND (t.Status <> s.Status
       OR (t.Status IS NULL AND s.Status IS NOT NULL)
       OR (t.Status IS NOT NULL AND s.Status IS NULL));

IF @@ROWCOUNT > @ApprovedMaximum
    THROW 50001, 'Update exceeded approved row limit', 1;

COMMIT TRANSACTION;

The target alias after UPDATE makes the write target unambiguous. The accepted answer’s UPDATE … FROM … JOIN form is valid; the missing production guard is a guarantee that the join provides only one value for each updated column occurrence.

XACT_ABORT ON makes many run-time errors roll back the transaction, but robust calling code should still use TRY/CATCH, test XACT_STATE(), roll back when needed, and rethrow. Keep the transaction short. Do validation and human review before opening it, while ensuring the validated stage cannot change—through an immutable batch, suitable isolation, or locking chosen with the DBA.

Audit evidence is not automatically durable

The OUTPUT clause can capture old and new values for each affected row, which is more useful than a single row count. If the audit target is inside the same transaction, it rolls back with the update. That is desirable for atomic business history but means it is not evidence of a rolled-back attempt. Record attempt metadata separately through an approved observability path without leaking sensitive row values.

Microsoft also notes that rows can be returned to a client from OUTPUT even when the statement later errors and rolls back. Do not treat streamed output as proof of commit; confirm transaction success independently.

Concurrency and operational trade-offs

• Lost updates: if another writer changes a target after preview, add an expected old value or rowversion to the update predicate and fail on a mismatch.
• Blocking: a large joined update holds locks, grows the transaction log, and can delay readers or replicas. Measure on production-like data and batch by a stable key when necessary.
• Batching: each committed batch weakens all-or-nothing semantics. Store progress and make retries idempotent.
• Indexes: index the staging batch/key and target key used by the join. Validate plans and statistics; never add a production index casually during an incident.
• Triggers and computed behavior: test effects on triggers, constraints, temporal tables, change capture, and downstream consumers.

Verification, rollback, and recovery

After commit, reconcile the committed audit count, target predicate count, application metrics, replication health, and a sample of business invariants. Run the same batch again in preview: an idempotent status synchronization should propose zero changes.

Before execution, take or verify a recoverable backup appropriate to the database’s recovery model and record the log/restore position. The cleanest rollback is often a compensating update from the captured old values, guarded so it changes only rows still holding the value written by this batch. If unrelated writers have since changed a row, stop for reconciliation rather than overwriting them. For broad corruption, restore to a separate database at a point in time, extract the affected rows, validate them, and repair selectively. Restoring the whole database may discard valid later transactions.

Attribution and current verification

This guide was prompted by jamesmhaley’s Stack Overflow question and Robin Day’s accepted answer, used under CC BY-SA. The joined-update syntax and its critical determinism warning were independently verified against Microsoft’s current official UPDATE documentation, with change capture checked against the official OUTPUT clause documentation and transaction error handling against SET XACT_ABORT.