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. La fusion 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) 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 { ALL BY NAME | SET <col_name> = <expr> [ , <col_name> = <expr> ... ] } | DELETE } [ ... ]
Copy
notMatchedClause ::=
   WHEN NOT MATCHED
     [ AND <case_predicate> ]
     THEN INSERT { ALL BY NAME | [ ( <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 ... 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)

WHEN MATCHED ... THEN { UPDATE { ALL BY NAME | SET ... } | DELETE }

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

ALL BY NAME

Met à jour toutes les colonnes de la table cible avec les valeurs de la source. Chaque colonne de la table cible est mise à jour avec les valeurs de la colonne portant le même nom à partir de la source.

La table cible et la source doivent avoir le même nombre de colonnes et les mêmes noms pour toutes les colonnes. Toutefois, l’ordre des colonnes peut être différent entre la table cible et la source.

SET col_name = expr [ , col_name = expr ... ]

Met à jour la colonne spécifiée dans la table cible en utilisant 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.

DELETE

Supprime les lignes de la table cible lorsqu’elles correspondent à la source.

notMatchedClause (pour les insertions)

WHEN NOT MATCHED ... 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)

WHEN NOT MATCHED ... THEN INSERT . { ALL BY NAME | [ ( col_name [ , ... ] ) ] VALUES ( expr [ , ... ] ) }

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

ALL BY NAME

insère toutes les colonnes de la table cible avec les valeurs de la source. Chaque colonne de la table cible est insérée avec les valeurs de la colonne portant le même nom dans la source.

La table cible et la source doivent avoir le même nombre de colonnes et les mêmes noms pour toutes les colonnes. Toutefois, l’ordre des colonnes peut être différent entre la table cible et la source.

( col_name [ , ... ] )

Spécifie éventuellement une ou plusieurs colonnes de la table cible à insérer avec les valeurs de la source.

Par défaut : aucune valeur (toutes les colonnes de la table cible sont 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.

Dupliquer le comportement de jointure

Lorsque plusieurs lignes de la table source correspondent à une seule ligne de la table cible, les résultats peuvent être déterministes ou non déterministes. Cette section décrit le comportement MERGE pour ces cas d’utilisation.

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 (p. 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 au moins une des 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

  • 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 la fonction 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 l’instruction 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 chaque copie dans la source. Pour un exemple, voir Effectuer une fusion avec des doublons de sources.

Exemples

Les exemples suivants utilisent la commande MERGE.

Effectuer une fusion de base qui met à jour les valeurs

L’exemple suivant effectue une fusion de base qui met à jour les valeurs de la table cible en utilisant des valeurs de la table source. Créez et chargez deux tables :

CREATE OR REPLACE TABLE merge_example_target (id INTEGER, description VARCHAR);

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

CREATE OR REPLACE TABLE merge_example_source (id INTEGER, description VARCHAR);

INSERT INTO merge_example_source (id, description) VALUES
  (10, 'To be updated (this is the new value)');
Copy

Affichez les valeurs dans les tables :

SELECT * FROM merge_example_target;
Copy
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the old value) |
+----+---------------------------------------+

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

Exécutez l’instruction MERGE :

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

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

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

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

Effectuer une fusion de base à l’aide de plusieurs opérations

Effectuez une fusion de base avec un mélange d’opérations (INSERT, UPDATE et DELETE).

Créez et chargez deux tables :

CREATE OR REPLACE TABLE merge_example_mult_target (
  id INTEGER,
  val INTEGER,
  status VARCHAR);

INSERT INTO merge_example_mult_target (id, val, status) VALUES
  (1, 10, 'Production'),
  (2, 20, 'Alpha'),
  (3, 30, 'Production');

CREATE OR REPLACE TABLE merge_example_mult_source (
  id INTEGER,
  marked VARCHAR,
  isnewstatus INTEGER,
  newval INTEGER,
  newstatus VARCHAR);

INSERT INTO merge_example_mult_source (id, marked, isnewstatus, newval, newstatus) VALUES
  (1, 'Y', 0, 10, 'Production'),
  (2, 'N', 1, 50, 'Beta'),
  (3, 'N', 0, 60, 'Deprecated'),
  (4, 'N', 0, 40, 'Production');
Copy

Affichez les valeurs dans les tables :

SELECT * FROM merge_example_mult_target;
Copy
+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  1 |  10 | Production |
|  2 |  20 | Alpha      |
|  3 |  30 | Production |
+----+-----+------------+

SELECT * FROM merge_example_mult_source;
Copy
+----+--------+-------------+--------+------------+
| ID | MARKED | ISNEWSTATUS | NEWVAL | NEWSTATUS  |
|----+--------+-------------+--------+------------|
|  1 | Y      |           0 |     10 | Production |
|  2 | N      |           1 |     50 | Beta       |
|  3 | N      |           0 |     60 | Deprecated |
|  4 | N      |           0 |     40 | Production |
+----+--------+-------------+--------+------------+

L’exemple de fusion suivant effectue les actions suivantes sur la table merge_example_mult_target :

  • Supprime la ligne contenant id défini sur 1 parce que la colonne marked pour la ligne avec le même id est Y dans merge_example_mult_source.

  • Met à jour les valeurs val et status de la ligne où id est défini sur 2 avec les valeurs dans la ligne avec le même id dans merge_example_mult_source, car isnewstatus est défini sur 1 pour la même ligne dans merge_example_mult_source.

  • Met à jour la valeur val de la ligne où id est défini sur 3 avec la valeur de la ligne avec le même id dans merge_example_mult_source. L’instruction MERGE ne met pas à jour la valeur status dans merge_example_mult_target car isnewstatus est défini sur 0 pour cette ligne dans merge_example_mult_source.

  • Insère la ligne où id est défini sur 4 car la ligne existe dans merge_example_mult_source et il n’y a pas de ligne correspondante dans merge_example_mult_target.

MERGE INTO merge_example_mult_target
  USING merge_example_mult_source
  ON merge_example_mult_target.id = merge_example_mult_source.id
  WHEN MATCHED AND merge_example_mult_source.marked = 'Y'
    THEN DELETE
  WHEN MATCHED AND merge_example_mult_source.isnewstatus = 1
    THEN UPDATE SET val = merge_example_mult_source.newval, status = merge_example_mult_source.newstatus
  WHEN MATCHED
    THEN UPDATE SET val = merge_example_mult_source.newval
  WHEN NOT MATCHED
    THEN INSERT (id, val, status) VALUES (
      merge_example_mult_source.id,
      merge_example_mult_source.newval,
      merge_example_mult_source.newstatus);
Copy
+-------------------------+------------------------+------------------------+
| number of rows inserted | number of rows updated | number of rows deleted |
|-------------------------+------------------------+------------------------|
|                       1 |                      2 |                      1 |
+-------------------------+------------------------+------------------------+

Pour voir les résultats de la fusion, affichez les valeurs dans la table merge_example_mult_target :

SELECT * FROM merge_example_mult_target ORDER BY id;
Copy
+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  2 |  50 | Beta       |
|  3 |  60 | Production |
|  4 |  40 | Production |
+----+-----+------------+

Effectuer une fusion à l’aide de ALL BY NAME

L’exemple suivant effectue une fusion qui insère et met à jour des valeurs dans la table cible en utilisant des valeurs de la table source. L’exemple utilise les sous-clauses WHEN MATCHED ... THEN ALL BY NAME et WHEN NOT MATCHED ... THEN ALL BY NAME pour spécifier que la fusion s’applique à toutes les colonnes.

Créez deux tables avec le même nombre de colonnes et les mêmes noms pour les colonnes, mais avec un ordre différent pour deux des colonnes :

CREATE OR REPLACE TABLE merge_example_target_all (
  id INTEGER,
  x INTEGER,
  y VARCHAR);

CREATE OR REPLACE TABLE merge_example_source_all (
  id INTEGER,
  y VARCHAR,
  x INTEGER);
Copy

Chargez les tables :

INSERT INTO merge_example_target_all (id, x, y) VALUES
  (1, 10, 'Skiing'),
  (2, 20, 'Snowboarding');

INSERT INTO merge_example_source_all (id, y, x) VALUES
  (1, 'Skiing', 10),
  (2, 'Snowboarding', 25),
  (3, 'Skating', 30);
Copy

Affichez les valeurs dans les tables :

SELECT * FROM merge_example_target_all;
Copy
+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 20 | Snowboarding |
+----+----+--------------+

SELECT * FROM merge_example_source_all;
Copy
+----+--------------+----+
| ID | Y            |  X |
|----+--------------+----|
|  1 | Skiing       | 10 |
|  2 | Snowboarding | 25 |
|  3 | Skating      | 30 |
+----+--------------+----+

Exécutez l’instruction MERGE :

MERGE INTO merge_example_target_all
  USING merge_example_source_all
  ON merge_example_target_all.id = merge_example_source_all.id
  WHEN MATCHED THEN
    UPDATE ALL BY NAME
  WHEN NOT MATCHED THEN
    INSERT ALL BY NAME;
Copy
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       1 |                      2 |
+-------------------------+------------------------+

Affichez les nouvelles valeurs dans la table cible :

SELECT *
  FROM merge_example_target_all
  ORDER BY id;
Copy
+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 25 | Snowboarding |
|  3 | 30 | Skating      |
+----+----+--------------+

Effectuer une fusion avec des doublons de sources

Effectuer une fusion dans laquelle la source a des valeurs en double et la cible n’a pas de valeurs correspondantes. Toutes les copies de l’enregistrement source sont insérées dans la cible. Pour plus d’informations, voir Résultats déterministes pour INSERT.

Tronquez les deux tables et chargez de nouvelles lignes dans la table source qui incluent les doublons :

TRUNCATE table merge_example_target;

TRUNCATE table merge_example_source;

INSERT INTO merge_example_source (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

La merge_example_target n’a aucune valeur. Affichez les valeurs dans la table merge_example_source :

SELECT * FROM merge_example_source;
Copy
+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

Exécutez l’instruction MERGE :

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

Affichez les nouvelles valeurs dans la table cible :

SELECT * FROM merge_example_target;
Copy
+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

Effectuer une fusion avec des résultats déterministes et non déterministes

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

Créez et chargez deux tables :

CREATE OR REPLACE TABLE merge_example_target_orig (k NUMBER, v NUMBER);

INSERT INTO merge_example_target_orig VALUES (0, 10);

CREATE OR REPLACE TABLE merge_example_src (k NUMBER, v NUMBER);

INSERT INTO merge_example_src VALUES (0, 11), (0, 12), (0, 13);
Copy

Lorsque vous effectuez la fusion dans l’exemple suivant, plusieurs mises à jour entrent en conflit les unes avec les autres. Si le paramètre de session ERROR_ON_NONDETERMINISTIC_MERGE est défini sur true, l’instruction MERGE renvoie une erreur. Sinon, l’instruction MERGE met à jour merge_example_target_clone.v avec une valeur (par exemple, 11, 12 ou 13) de l’une des lignes en double (ligne non définie) :

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

MERGE INTO  merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
Copy

Les mises à jour et les suppressions sont en conflit les unes avec les autres. Si le paramètre de session ERROR_ON_NONDETERMINISTIC_MERGE est défini sur true, l’instruction MERGE renvoie une erreur. Sinon, l’instruction MERGE supprime la ligne ou met à jour merge_example_target_clone.v avec une valeur (par exemple, 12 ou 13) de l’une des lignes en double (ligne non définie) :

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED AND merge_example_src.v = 11 THEN DELETE
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
Copy

Les suppressions multiples ne sont pas en conflit les unes avec les autres. Les valeurs jointes qui ne correspondent à aucune clause n’empêchent pas la suppression (merge_example_src.v = 13). L’instruction MERGE réussit et la ligne cible est supprimée :

CREATE OR REPLACE TABLE target CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED AND merge_example_src.v <= 12 THEN DELETE;
Copy

Les valeurs jointes qui ne correspondent à aucune clause n’empêchent pas une mise à jour (merge_example_src.v = 12, 13). L’instruction MERGE réussit et la ligne cible est définie sur target.v = 11 :

CREATE OR REPLACE TABLE merge_example_target_clone CLONE target_orig;

MERGE INTO merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED AND merge_example_src.v = 11
    THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
Copy

Utilisez GROUP BY dans la clause source pour vous assurer que chaque ligne cible se joint à une ligne dans la source :

CREATE OR REPLACE TABLE merge_example_target_clone CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING (SELECT k, MAX(v) AS v FROM merge_example_src GROUP BY k) AS b
  ON merge_example_target_clone.k = b.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);
Copy

Effectuer une fusion basée sur les valeurs DATE

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
Langage: Français