i

Description

Un meuble à fiches de bibliothèque

Source

MarkBuckawicki · page d’origine

Licence

CC0 Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Mise à jour par référence

Aller chercher une valeur de référence pour chaque ligne : le motif le plus courant du traitement ligne à ligne.

Sur cette page

1. Énoncé
2. Jeu de données
3. Procédural contre ensembliste
   1. Procédural
   2. Ensembliste
4. Résultat attendu
5. Pourquoi l'ensembliste est préférable
6. Pièges
7. Variantes
8. Vérifier l'équivalence
9. Versions de SQL Server

Énoncé#

Renseigner le taux de remise de chaque commande à partir de la catégorie de son client, sans réécrire les commandes dont le taux est déjà correct.

Jeu de données#

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);

Procédural contre ensembliste#

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;

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;

Résultat attendu#

Commandes 100 → 0,00 ; 101 → 5,00 ; 102 inchangée (5,00, déjà correcte) ; 103 → 10,00.

Pourquoi l'ensembliste est préférable#

• La valeur de référence n'est pas recherchée ligne par ligne : la jointure l'apporte pour toutes les commandes à la fois.
• Les mises à jour sont groupées dans une seule instruction : un seul plan, un seul passage, une seule validation.
• Le filtre WHERE évite de réécrire des lignes déjà correctes (moins de journal, moins de verrous).

Pièges#

• Déterminisme : si plusieurs lignes de la table source correspondent à une même ligne cible, la valeur retenue par UPDATE … FROM est arbitraire. Ici, les clés primaires de Clients et Categories garantissent l'unicité. Sans cette garantie, dédupliquer ou agréger la source d'abord.
• NULL : c.TauxRemise <> cat.TauxRemise n'est pas vrai si l'un des deux est NULL ; c'est pourquoi le test IS NULL est explicite.
• INNER ou LEFT : avec INNER JOIN, une commande sans correspondance n'est pas mise à jour (elle sort de l'ensemble). Avec LEFT JOIN, elle serait mise à jour avec NULL. Choisir selon l'intention, pas par habitude.

Variantes#

-- 1) Comparaison « null-safe » (avant SQL Server 2022)
WHERE EXISTS (SELECT c.TauxRemise EXCEPT SELECT cat.TauxRemise)

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

-- 3) Garder la trace de ce qui a changé
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;

Vérifier l'équivalence#

Un changement de méthode ne vaut que si le résultat est identique. Comparer dans les deux sens ; aucune ligne ne doit sortir.

-- Après avoir rejoué la même mise à jour sur une copie, comparer au résultat attendu :
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);
-- Résultat attendu : aucune ligne.

Versions de SQL Server#

UPDATE … FROM … JOIN et OUTPUT : SQL Server 2005 et ultérieur. IS DISTINCT FROM : SQL Server 2022 (niveau de compatibilité 160) et Azure SQL.