UPDATE with JOIN and CTE: Syntax, Pitfalls, and Performance

The Basic Pattern: UPDATE ... FROM ... JOIN

SQL Server does not support standard ANSI UPDATE ... JOIN syntax directly in the UPDATE clause. Instead, it uses a proprietary but very workable form: UPDATE the target table, then join to the source in a FROM clause.

UPDATE p
SET    p.UnitPrice = s.NewPrice
FROM   dbo.Products AS p
JOIN   dbo.PriceUpdates AS s
       ON s.ProductId = p.ProductId
WHERE  s.EffectiveDate <= GETDATE();

The alias in the UPDATE clause (p) must match the alias used for the target table in the FROM clause. This is the idiomatic SQL Server pattern — it works, it's fast, and every DBA on a SQL Server team will recognize it instantly.

The Trap: One-to-Many Joins Update an Unpredictable Row

This is the mistake that causes real incidents. If the join produces more than one matching row per target row, SQL Server does not error out — it silently picks one of the matches and applies it. Which one it picks is not guaranteed by the standard and can change between executions.

-- PriceUpdates has TWO rows for ProductId 100 (an accidental duplicate feed)
-- ProductId 100 | NewPrice 19.99 | EffectiveDate 2026-01-01
-- ProductId 100 | NewPrice 24.99 | EffectiveDate 2026-01-01

UPDATE p
SET    p.UnitPrice = s.NewPrice
FROM   dbo.Products AS p
JOIN   dbo.PriceUpdates AS s
       ON s.ProductId = p.ProductId
WHERE  s.EffectiveDate = '2026-01-01';
-- Product 100 ends up at EITHER 19.99 or 24.99 — you don't get to choose which

Guard Against It

Before running an update like this in production, verify the join is actually one-to-one on the target side:

-- Run this FIRST — if it returns any rows, your UPDATE will pick one arbitrarily
SELECT p.ProductId, COUNT(*) AS MatchCount
FROM   dbo.Products AS p
JOIN   dbo.PriceUpdates AS s
       ON s.ProductId = p.ProductId
WHERE  s.EffectiveDate = '2026-01-01'
GROUP  BY p.ProductId
HAVING COUNT(*) > 1;

If duplicates are possible by design (for example, taking "the most recent" source row), make the intent explicit with a CTE that deduplicates before the update — see below — rather than letting the join's arbitrary tie-break decide for you.

Updating Through a CTE

A common table expression is often the cleanest way to express "update using the most recent / highest-priority / deduplicated matching row," because you resolve the ambiguity before the UPDATE ever sees it.

WITH RankedUpdates AS (
    SELECT
        s.ProductId,
        s.NewPrice,
        ROW_NUMBER() OVER (
            PARTITION BY s.ProductId
            ORDER BY s.EffectiveDate DESC
        ) AS rn
    FROM dbo.PriceUpdates AS s
    WHERE s.EffectiveDate <= GETDATE()
)
UPDATE p
SET    p.UnitPrice = r.NewPrice
FROM   dbo.Products AS p
JOIN   RankedUpdates AS r
       ON r.ProductId = p.ProductId
      AND r.rn = 1;

Now there is exactly one candidate row per product — the most recent one — and the update is deterministic. The CTE also reads far more clearly than a nested subquery buried in the ON clause.

Updating a CTE Directly

You can also target the CTE itself in the UPDATE statement when the CTE wraps the table you actually want to change — useful for deduplication-style updates:

WITH DupeCheck AS (
    SELECT
        CustomerId,
        Email,
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerId) AS rn
    FROM dbo.Customers
)
UPDATE DupeCheck
SET    Email = NULL
WHERE  rn > 1;   -- clears email on duplicate rows, keeping the first

Performance: Keep It Set-Based

The temptation, especially when a join is complex, is to loop — a cursor that updates one row at a time. Resist it. A single set-based UPDATE ... FROM lets the optimizer choose a hash or merge join and apply the whole change in one pass; a row-by-row loop pays per-row overhead thousands of times over.

Approach	Typical behavior	When it's justified
Set-based UPDATE ... FROM	One optimized plan, minimal log overhead per row	Default choice — almost always
Cursor / row-by-row	Massive overhead, huge transaction log growth	Rare — only for genuinely row-dependent logic that can't be expressed in SQL
Batched set-based (UPDATE TOP (n) loop)	Set-based per batch, controlled log/lock footprint	Large tables where a single update would hold locks too long — see below

Batching Large Updates

An UPDATE ... FROM against millions of rows is still one transaction: it holds locks and grows the log for the full duration. On a busy OLTP table, batch it to keep each transaction short:

SET NOCOUNT ON;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (5000) p
    SET    p.UnitPrice = s.NewPrice
    FROM   dbo.Products AS p
    JOIN   dbo.PriceUpdates AS s
           ON s.ProductId = p.ProductId
    WHERE  p.UnitPrice <> s.NewPrice;   -- re-checked every loop, so finished rows drop out

    SET @RowsAffected = @@ROWCOUNT;
    -- Optional: WAITFOR DELAY '00:00:00.05'; to ease contention on a hot table
END;

The WHERE p.UnitPrice <> s.NewPrice condition matters twice: it skips rows that are already correct, and — combined with TOP (n) — it guarantees each loop iteration always finds fresh rows to update, so the loop terminates.

Check the Execution Plan, Not Just the Syntax

Correct syntax doesn't guarantee a good plan. Two things to check when an UPDATE ... FROM is slow:

• Is the join key indexed? An unindexed join column on either side forces a scan of the larger table for every row of the smaller one.
• Is the optimizer choosing a scan where you expected a seek? Stale statistics on the source table are a common cause — UPDATE STATISTICS on both tables before troubleshooting further.

The Bottom Line

SQL Server's UPDATE ... FROM ... JOIN syntax is fast and idiomatic, but it has one sharp edge: a one-to-many join updates an arbitrary matching row without warning. Verify the join is one-to-one before you ship the statement, use a ranked CTE when it legitimately isn't, keep large updates set-based and batched, and tune the join the same way you'd tune a SELECT.