Update by reference

Fetch a reference value for each row: the most common pattern of row-by-row processing.

≈ 3 min readReading

On this page

Problem statement
Data set
Procedural versus set-based
1. Procedural
2. Set-based
Expected result
Why set-based is preferable
Pitfalls
Variants
Verifying equivalence
SQL Server versions

Problem statement#

Fill in each order's discount rate from its customer's category, without rewriting the orders whose rate is already correct.

Data set#

CREATE TABLE dbo.Categories (CategorieId int NOT NULL PRIMARY KEY, TauxRemise decimal(5,2) NOT NULL);
CREATE TABLE dbo.Clients    (ClientId int NOT NULL PRIMARY KEY, CategorieId int NOT NULL REFERENCES dbo.Categories (CategorieId));
CREATE TABLE dbo.Commandes  (CommandeId int NOT NULL PRIMARY KEY, ClientId int NOT NULL REFERENCES dbo.Clients (ClientId), TauxRemise decimal(5,2) NULL);

INSERT INTO dbo.Categories (CategorieId, TauxRemise) VALUES (1, 0.00), (2, 5.00), (3, 10.00);
INSERT INTO dbo.Clients    (ClientId, CategorieId)   VALUES (10, 1), (20, 2), (30, 3);
INSERT INTO dbo.Commandes  (CommandeId, ClientId, TauxRemise)
VALUES (100, 10, NULL), (101, 20, NULL), (102, 20, 5.00), (103, 30, 0.00);

Procedural versus set-based#

Procedural#

DECLARE @CommandeId int, @ClientId int, @Taux decimal(5,2);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT CommandeId, ClientId FROM dbo.Commandes;
OPEN c;
FETCH NEXT FROM c INTO @CommandeId, @ClientId;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Taux = cat.TauxRemise
    FROM   dbo.Clients AS cl
    JOIN   dbo.Categories AS cat ON cat.CategorieId = cl.CategorieId
    WHERE  cl.ClientId = @ClientId;

    UPDATE dbo.Commandes
    SET    TauxRemise = @Taux
    WHERE  CommandeId = @CommandeId
      AND (TauxRemise IS NULL OR TauxRemise <> @Taux);

    FETCH NEXT FROM c INTO @CommandeId, @ClientId;
END;
CLOSE c;
DEALLOCATE c;

Set-based#

UPDATE c
SET    c.TauxRemise = cat.TauxRemise
FROM   dbo.Commandes  AS c
JOIN   dbo.Clients    AS cl  ON cl.ClientId = c.ClientId
JOIN   dbo.Categories AS cat ON cat.CategorieId = cl.CategorieId
WHERE  c.TauxRemise IS NULL
   OR  c.TauxRemise <> cat.TauxRemise;

Expected result#

Orders 100 → 0.00; 101 → 5.00; 102 unchanged (5.00, already correct); 103 → 10.00.

Why set-based is preferable#

The reference value is not looked up row by row: the join brings it in for all orders at once.
The updates are grouped in a single statement: one plan, one pass, one commit.
The WHERE filter avoids rewriting rows that are already correct (less logging, fewer locks).

Pitfalls#

Determinism: if several rows of the source table match the same target row, the value retained by UPDATE … FROM is arbitrary. Here, the primary keys of Clients and Categories guarantee uniqueness. Without that guarantee, deduplicate or aggregate the source first.
NULL: c.TauxRemise <> cat.TauxRemise is not true if either side is NULL; that is why the IS NULL test is explicit.
INNER or LEFT: with INNER JOIN, an order with no match is not updated (it drops out of the set). With LEFT JOIN, it would be updated with NULL. Choose according to intent, not out of habit.

Variants#

-- 1) "Null-safe" comparison (before SQL Server 2022)
WHERE EXISTS (SELECT c.TauxRemise EXCEPT SELECT cat.TauxRemise)

-- 2) SQL Server 2022 and Azure SQL
WHERE c.TauxRemise IS DISTINCT FROM cat.TauxRemise

-- 3) Keep a trace of what changed
UPDATE c
SET    c.TauxRemise = cat.TauxRemise
OUTPUT inserted.CommandeId, deleted.TauxRemise AS Ancien, inserted.TauxRemise AS Nouveau
INTO   dbo.Audit_Remises (CommandeId, Ancien, Nouveau)
FROM   dbo.Commandes AS c
JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
JOIN   dbo.Categories AS cat ON cat.CategorieId = cl.CategorieId
WHERE  c.TauxRemise IS DISTINCT FROM cat.TauxRemise;

Verifying equivalence#

A change of method is only worthwhile if the result is identical. Compare in both directions; no rows should come out.

-- After replaying the same update on a copy, compare with the expected result:
SELECT c.CommandeId, cat.TauxRemise AS Attendu, c.TauxRemise AS Obtenu
FROM   dbo.Commandes AS c
JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
JOIN   dbo.Categories AS cat ON cat.CategorieId = cl.CategorieId
WHERE  EXISTS (SELECT c.TauxRemise EXCEPT SELECT cat.TauxRemise);
-- Expected result: no rows.

SQL Server versions#

UPDATE … FROM … JOIN and OUTPUT: SQL Server 2005 and later. IS DISTINCT FROM: SQL Server 2022 (compatibility level 160) and Azure SQL.