MERGE¶

Insère, met à jour et supprime des valeurs dans une table en fonction des valeurs d’une deuxième table ou d’une sous-requête. Ceci peut être utile si la deuxième table est un journal de changements qui contient de nouvelles lignes (à insérer), des lignes modifiées (à mettre à jour) et/ou des lignes marquées (à supprimer) dans la table cible.

La commande prend en charge une sémantique pour traiter les cas suivants :

Valeurs qui correspondent (pour les mises à jour et les suppressions).
Valeurs qui ne correspondent pas (pour les insertions).

Voir aussi :: DELETE , UPDATE

Syntaxe¶

MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]

Copy

Où :

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]

Copy

notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Copy

Paramètres¶

target_table: Spécifie la table à fusionner.
source: Spécifie la table ou la sous-requête à joindre à la table cible.
join_expr: Spécifie l’expression sur laquelle joindre la table cible et la source.

`matchedClause` (pour les mises à jour ou les suppressions)¶

WHEN MATCHED ... THEN UPDATE <col_name> = <expr> | DELETE

Spécifie l’action à effectuer lorsque les valeurs correspondent.

AND case_predicate

Spécifie éventuellement une expression qui, lorsqu’elle est « true », provoque l’exécution de la casse correspondante.

Par défaut : aucune valeur (la casse correspondante est toujours exécutée)

SET col_name = expr [ … ]

Spécifie la colonne dans la table cible à mettre à jour ou à insérer et l’expression correspondante pour la nouvelle valeur de colonne (peut se référer à la fois aux relations cible et source).

Dans une seule sous-clause SET, vous pouvez spécifier plusieurs colonnes à mettre à jour/supprimer.

`notMatchedClause` (pour les insertions)¶

WHEN NOT MATCHED ... THEN INSERT

Spécifie l’action à effectuer lorsque les valeurs ne correspondent pas.

AND case_predicate

Spécifie éventuellement une expression qui, lorsqu’elle est « true », provoque l’exécution de la casse qui ne correspond pas.

Par défaut : aucune valeur (la casse non correspondante est toujours exécutée)

( col_name [ , ... ] )

Spécifie éventuellement une ou plusieurs colonnes dans la table cible à mettre à jour ou à insérer.

Par défaut : aucune valeur (toutes les colonnes de la table cible sont mises à jour ou insérées)

VALUES ( expr [ , ... ] )

Spécifie les expressions correspondantes pour les valeurs de colonne insérées (doivent se référer aux relations sources).

Notes sur l’utilisation¶

Une seule instruction MERGE peut comprendre plusieurs clauses de correspondance et de non-correspondance (p. ex. WHEN MATCHED ... et WHEN NOT MATCHED ...).
Toute clause de correspondance et de non-correspondance qui omet la sous-clause AND (comportement par défaut) doit être son dernier type de clause dans l’instruction (par exemple, une clause WHEN MATCHED ... ne peut être suivie d’une clause WHEN MATCHED AND ...). Cela entraîne une casse injoignable, qui renvoie une erreur.

Comportement de jointure en double¶

Résultats non déterministes pour UPDATE et DELETE¶

Lorsqu’une fusion joint une ligne de la table cible avec plusieurs lignes de la source, les conditions de jointure suivantes produisent des résultats non déterministes (c’est-à-dire que le système est incapable de déterminer la valeur source à utiliser pour mettre à jour ou supprimer la ligne cible) :

Une ligne cible est sélectionnée pour être mise à jour avec de multiples valeurs (par exemple WHEN MATCHED ... THEN UPDATE).
Une ligne cible est sélectionnée pour être à la fois mise à jour et supprimée (ex : WHEN MATCHED ... THEN UPDATE , WHEN MATCHED ... THEN DELETE).

Dans cette situation, le résultat de la fusion dépend de la valeur spécifiée pour le paramètre de session ERROR_ON_NONDETERMINISTIC_MERGE :

Si TRUE (valeur par défaut), la fusion renvoie une erreur.
Si FALSE, une ligne parmi les doublons est sélectionnée pour effectuer la mise à jour ou la suppression ; la ligne sélectionnée n’est pas définie.

Résultats déterministes pour UPDATE et DELETE¶

Les fusions déterministes sont toujours complètes et sans erreur. Une fusion est déterministe si elle remplit les conditions suivantes pour chaque ligne cible :

Une ou plusieurs lignes satisfont les clauses WHEN MATCHED ... THEN DELETE et aucune autre ligne source ne satisfait les clauses WHEN MATCHED

OR
Une seule ligne source satisfait une clause WHEN MATCHED ... THEN UPDATE et aucune autre ligne source ne satisfait les clauses WHEN MATCHED.

Cela rend MERGE sémantiquement équivalent aux commandes UPDATE et DELETE.

Note

Pour éviter les erreurs lorsque plusieurs lignes de la source de données (c’est-à-dire la table ou la sous-requête source) correspondent à la table cible en fonction de la condition ON, utilisez GROUP BY dans la clause source pour s’assurer que chaque ligne cible se joint à une ligne (au plus) dans la source.

Dans l’exemple suivant, supposons que src inclut plusieurs lignes avec la même valeur k. Les valeurs (v) qui seront utilisées pour mettre à jour les lignes de la ligne cible avec la même valeur de k sont ambiguës. En utilisant MAX() et GROUP BY, la requête précise exactement quelle valeur de v à partir de src est utilisée :

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

Résultats déterministes pour INSERT¶

Les fusions déterministes sont toujours complètes et sans erreur.

Si MERGE contient une clause WHEN NOT MATCHED ... THEN INSERT, et s’il n’y a pas de lignes correspondantes dans la cible, et si la source contient des valeurs en double, la cible obtient une copie de la ligne pour la copie each dans la source. (Un exemple est inclus ci-dessous).

Exemples¶

Effectuer une fusion simple :

Créer et charger les tables :

CREATE TABLE target_table (ID INTEGER, description VARCHAR);

CREATE TABLE source_table (ID INTEGER, description VARCHAR);

Copy

INSERT INTO target_table (ID, description) VALUES
    (10, 'To be updated (this is the old value)')
    ;

INSERT INTO source_table (ID, description) VALUES
    (10, 'To be updated (this is the new value)')
    ;

Copy

Exécuter l’instruction MERGE :

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description;
+------------------------+
| number of rows updated |
|------------------------|
|                      1 |
+------------------------+

Copy

Afficher la ou les nouvelles valeurs dans la table cible (la table source est inchangée) :

SELECT * FROM target_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+
SELECT * FROM source_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

Copy

Effectuer une fusion de base avec un mélange d’opérations (supprimer, mettre à jour, insérer) :

MERGE INTO t1 USING t2 ON t1.t1Key = t2.t2Key
    WHEN MATCHED AND t2.marked = 1 THEN DELETE
    WHEN MATCHED AND t2.isNewStatus = 1 THEN UPDATE SET val = t2.newVal, status = t2.newStatus
    WHEN MATCHED THEN UPDATE SET val = t2.newVal
    WHEN NOT MATCHED THEN INSERT (val, status) VALUES (t2.newVal, t2.newStatus);

Copy

Effectuer une fusion dans laquelle la source a des valeurs en double et la cible n’a pas de valeurs correspondantes. Noter que toutes les copies de l’enregistrement source sont insérées dans la cible :

Tronquer les deux tables et charger de nouvelles lignes dans la table source. Noter que les lignes contiennent des doublons.

TRUNCATE TABLE source_table;

TRUNCATE TABLE target_table;

INSERT INTO source_table (ID, description) VALUES
    (50, 'This is a duplicate in the source and has no match in target'),
    (50, 'This is a duplicate in the source and has no match in target')
    ;

Copy

Exécuter l’instruction MERGE :

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description
    WHEN NOT MATCHED THEN 
        INSERT (ID, description) VALUES (source_table.id, source_table.description);
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       2 |                      0 |
+-------------------------+------------------------+

Copy

Afficher la nouvelle valeur dans la table cible :

SELECT ID FROM target_table;
+----+
| ID |
|----|
| 50 |
| 50 |
+----+

Copy

Fusionner des enregistrements à l’aide de jointures qui produisent des résultats non déterministes et déterministes :

-- Setup for example.
CREATE TABLE target_orig (k NUMBER, v NUMBER);
INSERT INTO target_orig VALUES (0, 10);

CREATE TABLE src (k NUMBER, v NUMBER);
INSERT INTO src VALUES (0, 11), (0, 12), (0, 13);

-- Multiple updates conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise updates target.v with a value (e.g. 11, 12, or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Updates and deletes conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise either deletes the row or updates target.v with a value (e.g. 12 or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Multiple deletes do not conflict with each other;
-- joined values that do not match any clause do not prevent the delete (src.v = 13).
-- Merge succeeds and the target row is deleted.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v <= 12 THEN DELETE;

-- Joined values that do not match any clause do not prevent an update (src.v = 12, 13).
-- Merge succeeds and the target row is set to target.v = 11.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN UPDATE SET target.v = src.v;

-- Use GROUP BY in the source clause to ensure that each target row joins against one row
-- in the source:

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

Dans l’exemple suivant, la table members enregistre les noms, adresses et frais actuels (members.fee) versés à une salle de sport locale. La table signup enregistre la date d’inscription de chaque membre (signup.date). L’instruction MERGE applique des frais standard de 40 $ aux membres qui se sont inscrits à la salle il y a plus de 30 jours, après l’expiration de l’essai gratuit :

MERGE INTO members m
  USING (
  SELECT id, date
  FROM signup
  WHERE DATEDIFF(day, CURRENT_DATE(), signup.date::DATE) < -30) s ON m.id = s.id
  WHEN MATCHED THEN UPDATE SET m.fee = 40;

Copy

MERGE¶

Syntaxe¶

Paramètres¶

matchedClause (pour les mises à jour ou les suppressions)¶

notMatchedClause (pour les insertions)¶

Notes sur l’utilisation¶

Comportement de jointure en double¶

Résultats non déterministes pour UPDATE et DELETE¶

Résultats déterministes pour UPDATE et DELETE¶

Résultats déterministes pour INSERT¶

Exemples¶

`matchedClause` (pour les mises à jour ou les suppressions)¶

`notMatchedClause` (pour les insertions)¶