Exemples
MERGE INTO books b
USING purchases p
ON p.title = b.title AND p.booktype = 'bk'
WHEN MATCHED THEN
UPDATE SET b.descr = b.descr || '; ' || p.descr
WHEN NOT MATCHED THEN
INSERT (title, descr, bought)
VALUES (p.title, p.descr, p.bought);
-- utiliser une table de dérivation
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (cd.id, cd.name);
-- ainsi que la récurrence CTE
MERGE INTO numbers
USING (
WITH RECURSIVE r(n) AS (
SELECT 1 FROM rdb$database
UNION ALL
SELECT n+1 FROM r WHERE n < 200
)
SELECT n FROM r
) t
ON numbers.num = t.n
WHEN NOT MATCHED THEN
INSERT(num) VALUES(t.n);
-- en utilisant la clause DELETE
MERGE INTO SALARY_HISTORY
USING (
SELECT EMP_NO
FROM EMPLOYEE
WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE
MERGE
avec des conditions supplémentaires dans les clauses WHEN [NOT] MATCHED
.Dans l'exemple suivant, la table PRODUCT_INVENTORY
est mise à jour quotidiennement en fonction des commandes traitées dans la table SALES_ORDER_LINE
. Si le nombre de commandes pour un produit est tel que le niveau de stock du produit tombe à zéro ou devient encore plus bas, la ligne pour ce produit est supprimée de la table PRODUCT_INVENTORY
.
MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
SELECT
SL.ID_PRODUCT,
SUM(SL.QUANTITY)
FROM SALES_ORDER_LINE SL
JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
WHERE S.BYDATE = CURRENT_DATE
GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
TARGET.BYDATE = CURRENT_DATE
L'exemple suivant met à jour les enregistrements de la table cible s'ils sont trouvés dans la table source et les supprime s'ils ne sont pas trouvés.
MERGE
INTO customers c
USING new_customers nc
ON (c.id = nc.id)
WHEN MATCHED THEN
UPDATE SET
name = cd.name
WHEN NOT MATCHED BY SOURCE THEN
DELETE
RETURNING
L'instruction MERGE
peut contenir une construction RETURNING
pour retourner les valeurs des lignes ajoutées, modifiées ou supprimées. Le RETURNING
peut contenir toutes les colonnes de la table cible (la vue mise à jour), pas nécessairement toutes, ainsi que d'autres colonnes et expressions.
Les valeurs de retour contiennent les changements effectués dans les déclencheurs BEFORE
.
Les noms de colonnes peuvent être préfixés avec NEW
et OLD
pour spécifier quelle valeur de colonne vous voulez avant ou après la modification.
Un astérisque (*) peut être spécifié à la place d'une liste de colonnes, auquel cas toutes les colonnes de la table cible seront retournées. Les préfixes NEW
et OLD
peuvent être utilisés en conjonction avec un astérisque.
Pour les clauses WHEN MATCHED UPDATE
et MERGE WHEN NOT MATCHED
, les noms de colonne non spécifiés ou spécifiés par ou leurs alias sont compris comme des colonnes préfixées avec NEW
, pour les clauses MERGE WHEN MATCHED DELETE
- avec le préfixe OLD
.
Note |
|
MERGE
avec la clause RETURNING
.Modifions un peu notre exemple précédent pour qu'il ne concerne qu'une seule ligne, et ajoutons une instruction RETURNING
qui renvoie l'ancienne et la nouvelle quantité de l'article et la différence entre ces valeurs.
MERGE INTO PRODUCT_IVENTORY AS TARGET
USING (
SELECT
SL.ID_PRODUCT,
SUM(SL.QUANTITY)
FROM SALES_ORDER_LINE SL
JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
WHERE S.BYDATE = CURRENT_DATE
AND SL.ID_PRODUCT = :ID_PRODUCT
GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO :OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY