UPDATE With a JOIN: Changing Rows From Another Table Safely

An update finishes without an error, yet the chosen value is wrong. UPDATE with a JOIN needs one clear source row for each target. Duplicate matches make that choice undefined even when the statement succeeds.

Establish One Source Row per Target

An UPDATE with a JOIN finds replacement values through matching source rows. The target row doesn't reliably receive several successive updates when multiple source rows match. SQL Server can choose one matching source value without a defined winner.

An ORDER BY elsewhere doesn't fix that. Reduce the source to one row per target using a documented business rule before making the change.

I check source uniqueness before reviewing the assignment expression. An accurate calculation on an ambiguous source still produces an unreliable update. Use temporary tables for the examples below.

The source primary key encodes the rule for this small demonstration. In a real load, validate staging keys before applying them. Don't infer uniqueness from a clean sample returned by TOP.

CREATE TABLE #Prices(ProductId int PRIMARY KEY, Price decimal(19,4) NOT NULL);
CREATE TABLE #NewPrices(ProductId int PRIMARY KEY, Price decimal(19,4) NOT NULL);
INSERT #Prices VALUES (1,10),(2,20);
INSERT #NewPrices VALUES (1,12),(2,22);
SELECT p.ProductId, p.Price AS OldPrice, n.Price AS NewPrice
FROM #Prices AS p
JOIN #NewPrices AS n ON n.ProductId = p.ProductId;

Preview the Rows an UPDATE With a JOIN Will Change

Use the same joins and predicates in the preview that you intend to update. Inspect unmatched source rows and unmatched targets separately. An inner join changes only matching rows.

That is useful when the business rule expects partial updates. It is dangerous when someone assumes the missing targets received a default. State that behavior before approving the affected set.

Duplicate detection belongs on the actual source query, including joins. A base table with a unique key can become duplicated after joining detail rows. Group the final source by the target's complete key.

Include TenantId or another partitioning identity where needed. The question is uniqueness at the update's grain, not uniqueness in one convenient source table.

SELECT ProductId, COUNT_BIG(*) AS SourceMatches
FROM #NewPrices
GROUP BY ProductId
HAVING COUNT_BIG(*) > 1;
SELECT n.ProductId
FROM #NewPrices AS n
LEFT JOIN #Prices AS p ON p.ProductId = n.ProductId
WHERE p.ProductId IS NULL;

Use the Alias as the Target of an UPDATE With a JOIN

The UPDATE alias FROM form identifies the target clearly. Keep that alias consistent in the SET clause and joins. Qualify every column that appears on both sides.

A short query isn't worth ambiguity when it changes data. OUTPUT captures the old and new values. Review those changes directly instead of inferring them from a later SELECT.

The transaction below is a rehearsal and ends with ROLLBACK. Capture @@ROWCOUNT immediately because later statements replace it. The expected count is a demonstration value you choose, not an observed production result.

Use a business-approved expectation for real work. SET XACT_ABORT and TRY/CATCH help keep an error from leaving an open transaction behind in the query window.

SET XACT_ABORT ON;
DECLARE @Expected int = 2;
DECLARE @Changed int;
BEGIN TRY
    BEGIN TRAN;
    UPDATE p
    SET Price = n.Price
    OUTPUT inserted.ProductId, deleted.Price AS OldPrice, inserted.Price AS NewPrice
    FROM #Prices AS p
    JOIN #NewPrices AS n ON n.ProductId = p.ProductId;
    SET @Changed = @@ROWCOUNT;
    IF @Changed <> @Expected
        THROW 50001, 'Unexpected affected row count.', 1;
    ROLLBACK;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

Compare the Scalar Subquery Form

A scalar subquery returns one value for each target row. If it returns multiple rows, SQL Server raises an error instead of choosing an undefined source winner. That is a useful diagnostic difference.

It still needs a business rule for duplicates. Errors don't decide whether the newest value or the approved value belongs in the target.

Include an EXISTS predicate to avoid assigning NULL when no source matches. Without it, a scalar subquery returning no rows yields NULL. A NOT NULL target then errors, while a nullable target silently changes meaning.

The following rehearsal keeps the same matching set as the join version. Read both forms against that shared contract rather than choosing purely by appearance.

BEGIN TRAN;
UPDATE p
SET Price = (SELECT n.Price FROM #NewPrices AS n WHERE n.ProductId = p.ProductId)
FROM #Prices AS p
WHERE EXISTS (SELECT 1 FROM #NewPrices AS n WHERE n.ProductId = p.ProductId);
ROLLBACK;

Understand MERGE Without Assuming Safety

MERGE expresses matching and assignment in one statement. Multiple matching source rows that update the same target produce an error. That doesn't remove the need for a unique source.

Concurrency, triggers, and the chosen matching predicates still need review. For an update-only task, a conventional UPDATE remains easy to understand and verify. More syntax isn't automatically more protection.

Keep nonmatching behavior explicit if you later add inserts or deletes. A broad source filter doesn't necessarily define the complete target population. Deleting everything absent from a partial source is a common mistake.

The example below performs only the same matched update and rolls back. It lets you compare behavior without introducing a second task into the demonstration.

BEGIN TRAN;
MERGE #Prices AS p
USING #NewPrices AS n ON n.ProductId = p.ProductId
WHEN MATCHED THEN UPDATE SET Price = n.Price;
ROLLBACK;

Close the Gap Between Check and Change

A preview and a later update are separate observations. Another session can change the source between them. Protect the approved set with suitable transaction isolation, locking, or a stable staged source.

Unique constraints enforce source identity even when two sessions race. Decide that protection with the workload's concurrency requirements. A screenshot of the preview doesn't reserve those rows.

I also check target triggers before estimating the impact. They can write other tables or perform additional validation. OUTPUT reports the update's values, but it isn't a substitute for reviewing trigger effects.

Include those effects in the rehearsal. A correct target row count can coexist with an unwanted downstream action. The transaction's complete behavior is the unit you approve.

Keep a Reviewable Record of Each UPDATE With a JOIN

Which source row should win if two arrive for the same product? Answer that in staging and enforce it before the update. Record the approved keys and replacement values.

Use a committed change only after the rehearsal matches the intended set. Preserve a recovery copy outside the transaction because an OUTPUT stream can appear even when the transaction later rolls back.

A row-count check catches broad mistakes, but not every wrong assignment. Review keys and values too. Compare a post-change SELECT with the approved source under the same business rule.

An UPDATE with a JOIN can succeed and still assign incorrect values. Treat uniqueness, scope, and transaction handling as separate checks. Together they make a small statement safe enough to trust with real data.

For a large change, plan batches using a stable target key. Each batch needs its own approved scope and durable progress record. Don't repeat TOP without a predicate that excludes completed keys. A retry should continue the intended change, rather than selecting an arbitrary set of rows again under a different plan.

Related reading on this blog: UPDATE Without WHERE Clause: The Day Everyone Got a Raise and Modern Explicit JOIN Syntax: A Brief Note.

A joined update is not an arbitrary choice, it is one approved source value per target.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.