SQL Server UPDATE FROM JOIN: a safe, runnable example

UPDATE ... FROM changes many target rows using values from another table in one statement. It is a common replacement for a cursor that repeatedly looks up a value and updates one row. The main risk is multiple source rows matching the same target row: SQL Server does not define which source value wins. Microsoft's UPDATE reference calls out this ambiguity.

Start with one source row per key

This complete example updates two customer records. Temporary tables keep it isolated from your real data:

CREATE TABLE #Customer (
    CustomerId int PRIMARY KEY,
    Tier varchar(20) NOT NULL
);
CREATE TABLE #ApprovedTier (
    CustomerId int PRIMARY KEY,
    NewTier varchar(20) NOT NULL
);

INSERT INTO #Customer (CustomerId, Tier)
VALUES (1, 'Standard'), (2, 'Standard'), (3, 'Gold');
INSERT INTO #ApprovedTier (CustomerId, NewTier)
VALUES (1, 'Gold'), (2, 'Silver');

UPDATE c
SET c.Tier = a.NewTier
FROM #Customer AS c
JOIN #ApprovedTier AS a
  ON a.CustomerId = c.CustomerId
WHERE c.Tier <> a.NewTier;

SELECT CustomerId, Tier
FROM #Customer
ORDER BY CustomerId;

The result should be (1, Gold), (2, Silver), and (3, Gold). The source table's primary key guarantees one approved tier per customer. An inner join leaves customers without an approved change untouched.

Check a staging table before updating

Incoming files often contain duplicate keys. Check them before you run the update:

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

If this returns rows, decide which record is authoritative. A timestamp alone may still tie; include a unique staging ID to make the choice deterministic. The following pattern selects the most recent record for each customer:

WITH RankedChange AS (
    SELECT CustomerId, NewTier,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerId
               ORDER BY ApprovedAt DESC, StagingId DESC
           ) AS rn
    FROM dbo.TierStaging
    WHERE IsApproved = 1
)
UPDATE c
SET c.Tier = r.NewTier
FROM dbo.Customer AS c
JOIN RankedChange AS r
  ON r.CustomerId = c.CustomerId
 AND r.rn = 1
WHERE c.Tier <> r.NewTier;

Replace the table and column names with your own. The ROW_NUMBER() rule is a business decision: if the latest approved record is not the right one, choose a different rule. For related ranking examples, see SQL window functions.

What about NULL values?

c.Tier <> r.NewTier excludes rows where either value is NULL, because the comparison is unknown. If nulls are allowed, use an explicit null-aware condition:

WHERE c.Tier <> r.NewTier
   OR (c.Tier IS NULL AND r.NewTier IS NOT NULL)
   OR (c.Tier IS NOT NULL AND r.NewTier IS NULL);

For a production change, preview the join with SELECT, count the target keys, run in a transaction where appropriate, and inspect the changed values. To retain old and new values automatically, use the SQL Server OUTPUT clause. For a broader decision guide, see when to replace a SQL cursor.