MERGE — Insère, met à jour ou efface des lignes d'une table selon une condition
[ WITHclause_with
[, ...] ] MERGE INTO [ ONLY ]nom_table_cible
[ * ] [ [ AS ]alias_cible
] USINGsource_des_données
ONcondition_de_jointure
clause_when
[...] oùsource_des_données
est : { [ ONLY ]nom_table_source
[ * ] | (requête_source
) } [ [ AS ]alias_source
] etclause_when
est : { WHEN MATCHED [ ANDcondition
] THEN {merge_update
|merge_delete
| DO NOTHING } | WHEN NOT MATCHED [ ANDcondition
] THEN {merge_insert
| DO NOTHING } } etmerge_insert
est : INSERT [(nom_de_colonne
[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {expression
| DEFAULT } [, ...] ) | DEFAULT VALUES } etmerge_update
est : UPDATE SET {nom_de_colonne
= {expression
| DEFAULT } | (nom_de_colonne
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (nom_de_colonne
[, ...] ) = (sous-SELECT
) etmerge_delete
est : DELETE
MERGE
exécute des actions qui modifient des lignes dans
la table cible identifiée par
nom_table_cible
, à partir d'une
source_des_données
.
MERGE
est un ordre SQL qui, à lui seul
et selon la condition, peut opérer un INSERT
, un
UPDATE
ou un DELETE
sur des lignes, une
tâche qui nécessiterait sinon de multiples appels en langage procédural.
Tout d'abord, la commande MERGE
fait une jointure entre
source_des_données
et
la table cible, ce qui donne
zéro ou plus ligne(s) candidates au changement. Pour chacune, on définit le
statut MATCHED
ou NOT MATCHED
une fois
pour toutes ; puis les clauses WHEN
sont évaluées
dans l'ordre spécifié. Pour chaque ligne candidate à un changement, la
première clause évaluée à true est exécutée. Au plus une clause
WHEN
est exécutée pour chaque ligne candidate au
changement.
Les opérations de MERGE
ont le même effet que les
commandes UPDATE
, INSERT
, ou
DELETE
de même nom. La syntaxe de ces commandes diffère,
notamment sur l'absence de clause WHERE
et de nom de
table. Toutes les actions opèrent sur la table cible,
bien que des modifications
sur d'autres tables puissent être faites en passant par des triggers.
Si DO NOTHING
est précisé, la ligne source est passée.
Comme les actions sont évaluées dans l'ordre spécifié, il peut être pratique
de sauter les lignes source sans intérêt avant une gestion plus fine.
Il n'y a pas de droit MERGE
dédié. Si vous spécifiez une
mise à jour, vous devez posséder le droit UPDATE
sur les
colonnes de la table cible mentionnées dans la clause SET
.
Pour une insertion, vous devez posséder le droit INSERT
sur la table cible. Et pour une suppression, vous devez posséder le droit
DELETE
sur la table cible. Si vous ajoutez une action
DO NOTHING
, vous devrez avoir le droit
SELECT
sur au moins une colonne de la table cible. Vous
aurez aussi besoin du droit SELECT
sur toute colonne de
source_des_données
et sur toute
colonne de la table cible référencée dans toute condition
(incluant condition_jointure
) ou
expression
. Les droits sont testés et vérifiés une fois
au démarrage de l'ordre, que les clauses WHEN
concernées
soient exécutées ou pas.
MERGE
n'est pas supporté si la table cible
est une vue matérialisée, une
table étrangère, ou si des règles y ont été posées.
requête_with
La clause WITH
vous permet d'ajouter une ou plusieurs
sous-requêtes pouvant être référencées dans la requête
MERGE
. Voir Section 7.8 et
SELECT pour des détails. Notez que
WITH RECURSIVE
n'est pas supporté par
MERGE
.
nom_table_cible
Nom (éventuellement qualifié du schéma) de la table cible où fusionner des
informations.
Si ONLY
est indiqué avant le nom de la table, les
lignes correspondantes sont mises à jour ou supprimées uniquement dans la
table nommée. Si ONLY
n'est pas indiquée, les lignes
correspondantes sont aussi mises à jour ou supprimées des tables héritant
de la table nommée. *
peut être indiquée en option
après le nom de la table pour indiquer explicitement que les tables
enfants sont inclus. Le mot clé ONLY
et l'option
*
n'affectent pas les actions d'insertion, qui insèrent
toujours uniquement dans la table nommée.
alias_cible
Un nom à substituer à la table cible. Quand un alias est fourni, il cache
complètement le nom réel de la table. Par exemple, avec MERGE
INTO foo AS f
, le reste de l'ordre MERGE
devra appeler la table avec f
et non
foo
.
nom_table_source
Le nom (éventuellement qualifié du schéma) de la table, vue ou table de
transition source.
Si ONLY
est précisé avant le nom de la table, les
lignes correspondantes sont inclus uniquement à partir de la table nommée.
Si ONLY
n'est pas indiqué, les lignes correspondantes
sont aussi incluses à partir des tables héritant de la table nommée. En
option, *
peut être ajouté après le nom de la table
pour indiquer explicitement que les tables enfants sont incluses.
requête_source
Une requête (commande SELECT
ou
VALUES
) fournissant les lignes à fusionner dans
la table cible. Se référer
à la documentation de SELECT ou VALUES pour une description des syntaxes.
alias_source
Un nom substitut de la source de données. Quand un alias est fourni, il cache complètement le nom réel de la table ou le fait qu'une requête ait été fournie.
condition_de_jointure
condition_de_jointure
est une
expression dont le résultat est un boolean
(similaire à une
clause WHERE
), lequel indique quelles lignes de
source_des_données
correspondent à des lignes dans la table cible.
Seules les colonnes de la table cible que l'on tente de
rapprocher de source_des_données
devraient apparaître
dans condition_de_jointure
.
Les sous-expressions de la condition qui ne référencent que la table
cible peuvent affecter le choix
de l'action, souvent de manière surprenante.
clause_when
Au moins une clause WHEN
est nécessaire.
Si la clause WHEN
indique WHEN
MATCHED
et que la ligne candidate correspond à une ligne de
la table cible, et si
condition
est absente ou
évaluée à true
, alors la clause WHEN
est exécutée.
À l'inverse, si la clause WHEN
indique WHEN
NOT MATCHED
, et que la ligne candidate n'a pas de ligne
correspondante dans la table cible, et si condition
est absente ou évaluée à
true
, alors la clause WHEN
est
exécutée.
condition
Une expression dont le résultat est un boolean
. Si cette
expression renvoie true
, alors la clause
WHEN
qu'elle accompagne est exécutée pour la ligne en
cours.
Une condition d'une clause WHEN MATCHED
peut se référer
à des colonnes de la relation source comme de la cible. Une condition
d'une clause WHEN NOT MATCHED
ne peut se référer qu'à
des colonnes de la relation source, puisque, par définition, il n'y a pas
de ligne correspondante dans la cible. Seuls les attributs système de la
table cible sont accessibles.
merge_insert
Spécification de la clause INSERT
qui insère une ou
plusieurs lignes dans la table cible. Les noms des colonnes cibles peuvent
être listés dans n'importe quel ordre. Si aucune liste n'est donnée, le
défaut est l'ensemble des colonnes de la table dans leur ordre de
déclaration.
Chaque colonne absente de la liste des champs, explicite ou implicite, sera remplie par une valeur par défaut, soit sa valeur par défaut déclarée, soit NULL.
Si la table cible est une table partitionnée, chaque ligne est routée vers la partition appropriée et insérée dedans. Si la table cible est une partition, une erreur se produira si une ligne quelconque viole la contrainte de partitionnement.
Les noms de colonnes ne peuvent être spécifiés qu'une fois. Les commandes
INSERT
ne peuvent pas contenir des sous-SELECT.
Une seule clause VALUES
peut être spécifiée. Elle ne
peut se référer qu'à des colonnes de la table source, puisque par
définition il n'y a pas de ligne cible correspondante.
merge_update
Spécification de la commande UPDATE
qui met à jour la
ligne courante de la table cible. Les noms de colonnes ne
peuvent être utilisés plus d'une fois.
Ne sont permis ni un nom de table, ni une clause WHERE
.
merge_delete
Spécifie une action DELETE
qui efface la ligne courante
de la table cible.
N'incluez ni le nom de la table ni aucune autre clause, comme vous le
feriez pour une commande DELETE.
nom_de_colonne
Le nom d'une colonne dans la table cible. Au besoin, il peut être complété avec un nom de sous-champ ou un indice de tableau. (N'insérer que dans certains champs d'une colonne composée laisse les autres champs à NULL). N'incluez pas le nom de la table dans la définition d'une colonne cible.
OVERRIDING SYSTEM VALUE
Sans cette clause, une erreur sera levée si une valeur explicite (autre
que DEFAULT
) est spécifiée pour une colonne identité
définie comme GENERATED ALWAYS
. Cette clause passe
outre à la restriction.
OVERRIDING USER VALUE
Avec cette clause, toutes les valeurs fournies pour des colonnes identité
définies comme GENERATED BY DEFAULT
sont ignorées, et
les valeurs générées par séquence sont appliquées.
DEFAULT VALUES
Toutes les colonnes seront remplies avec leur valeur par défaut. (Une
clause OVERRIDING
n'est alors pas permise.)
expression
Une expression à assigner à une colonne. Au sein d'une clause
WHEN MATCHED
, elle peut utiliser des valeurs de la
ligne originale dans la table cible, et des valeurs de la ligne issue de
source_des_données
. Dans une clause WHEN NOT
MATCHED
, l'expression peut utiliser des valeurs de
source_des_données
.
DEFAULT
Positionne une colonne à sa valeur par défaut (qui sera
NULL
si aucune n'a été définie).
sous-SELECT
Une sous-requête SELECT
qui produit autant de colonnes
en sortie que celles listées dans la liste de colonnes entre parenthèses
la précédent. La sous-requête ne doit pas renvoyer plus d'une ligne quand
elle est exécutée. Si elle renvoie une ligne, la valeur de ses colonnes
est affectée aux colonnes cibles ; si elle ne renvoie aucune ligne,
des valeurs NULL sont affectées aux colonnes cibles. La sous-requête
peut faire référence aux valeurs de la ligne originale dans la table
cible, et aux valeurs provenant de la ligne
source_de_donnees
.
Si elle termine avec succès, la commande MERGE
renvoie un
résultat de la forme :
MERGE nombre_total
nombre_total
est le nombre total
des lignes changées (qu'elles aient été insérées, mises à jour ou effacées).
Si nombre_total
vaut 0, aucune
ligne n'a été changée de quelque manière que ce soit.
Les étapes suivantes se déroulent pendant l'exécution
de MERGE
.
Exécute tous les triggers BEFORE STATEMENT
de chaque
action spécifiée, que leur clause WHEN
renvoie quelque
chose ou non.
Fait une jointure entre la source et la table cible. La requête résultante sera optimisée comme à l'habitude, et produira un ensemble de lignes candidates au changement. Pour chacune :
Évalue pour chaque ligne si elle a une correspondance
(MATCHED
) ou pas (NOT MATCHED
).
Teste chaque condition WHEN
dans l'ordre spécifié
jusqu'à ce qu'une renvoie true.
Quand une condition renvoie true, exécute les actions suivantes :
exécute tous les triggers BEFORE ROW
qui se
déclenchent sur ce type d'action ;
exécute l'action spécifiée, en vérifiant toutes les contraintes sur la table cible ;
exécute tous les triggers AFTER ROW
qui se
déclenchent sur ce type d'action.
Exécute tous les triggers AFTER STATEMENT
pour les
actions spécifiées, qu'elles se soient ou non réellement produites. C'est
équivalent à un ordre UPDATE
qui ne modifie aucune
ligne.
En résumé, les triggers de niveau instruction (par exemple
INSERT
) seront déclenchés à chaque
spécification d'une action de leur genre. Par contre,
les triggers de niveau ligne se déclencheront uniquement pour les types
d'événements exécutés. Ainsi un ordre
MERGE
peut déclencher à la fois des triggers sur
instruction UPDATE
et sur INSERT
, même
si seuls des triggers de ligne sur UPDATE
sont déclenchés.
Vous devez vous assurer que la jointure produit au plus une ligne candidate
pour chaque ligne de la source. En d'autres termes, une ligne cible ne
devrait pas être jointe à plus d'une ligne de données source. Si cela devait
arriver, une seule ligne parmi les candidates sera utilisée pour modifier la
ligne cible ; une erreur sera levée lors des tentatives suivantes de
modification de la ligne. Cela peut arriver si des triggers sur ligne
modifient la table cible et que les lignes modifiées sont par la suite aussi
modifiées par MERGE
. Si l'action répétée est un
INSERT
, cela provoquera une violation d'unicité, alors que
des ordres UPDATE
ou DELETE
répétés
causeront une violation de cardinalité ; ce dernier comportement est
exigé par le standard SQL. Cela diffère du comportement
historique de PostgreSQL dans les jointures au
sein d'ordres UPDATE
et DELETE
, où le
deuxième essai de modification d'une même ligne est simplement ignoré, tout
comme les suivants.
Si une clause WHEN
omet une sous-clause
AND
, elle devient la clause finale atteinte pour ce type
(MATCHED
ou NOT MATCHED
). Si une autre
clause WHEN
est spécifiée pour le même type, elle serait
probablement inatteignable et une erreur est levée. Si aucune clause finale
n'est spécifiée pour un type, il est possible qu'aucune action ne soit
exécutée pour la ligne candidate.
L'ordre dans lequel les lignes seront générées depuis la source des données
est par défaut indéterminé. Au besoin, une requête_source
peut être utilisée pour
définir un ordre cohérent, qui pourrait être nécessaire pour éviter des
verrous mutuels entre transactions simultanées.
Il n'y a pas de clause RETURNING
à une commande
MERGE
. Les action INSERT
,
UPDATE
et DELETE
ne peuvent contenir de
clauses RETURNING
ou WITH
.
Quand MERGE
est lancé en parallèle à d'autres commandes
qui modifient la table cible, les règles usuelles d'isolation des
transactions s'appliquent ; voir Section 13.2 pour
une explication du comportement de chaque niveau d'isolation. Comme
alternative permettant de lancer un ordre UPDATE
en même
temps que se déroule un INSERT
, vous pouvez considérer
INSERT ... ON CONFLICT
. Il y a beaucoup de différences
entre les deux ordres, et leurs limites diffèrent, si bien qu'ils ne sont pas
interchangeables.
Effectue une maintenance sur customer_accounts
à partir de
nouvelles recent_transactions
.
MERGE INTO customer_account ca USING recent_transactions t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);
Notez que c'est exactement la même chose que l'ordre suivant, car le résultat
du MATCHED
ne change pas pendant l'exécution.
MERGE INTO customer_account ca USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);
Tente d'insérer un nouvel article dans un stock avec sa quantité. Si l'article existe déjà, met plutôt à jour son stock existant. Interdit les entrées avec un stock à zéro.
MERGE INTO wines w USING wine_stock_changes s ON s.winename = w.winename WHEN NOT MATCHED AND s.stock_delta > 0 THEN INSERT VALUES(s.winename, s.stock_delta) WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN UPDATE SET stock = w.stock + s.stock_delta WHEN MATCHED THEN DELETE;
La table wine_stock_changes
peut être, par exemple, une
table temporaire récemment chargée dans la base.
Cette commande se conforme au standard SQL.
La clause WITH
et l'action DO NOTHING
sont des extensions au standard SQL.