Location>code7788 >text

SQL Advanced Syntax MERGE INTO

Popularity:275 ℃/2024-09-11 15:37:55

Based on the result of the join with the source table, insert, update, delete, and other operations are performed on the target table. For example, for the target table, if data exists in the source table, it is updated, and if it does not, it is inserted, you can use MEREG for synchronization.

basic grammar

MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN 
XXX
WHEN NOT MATCHED THEN 
XXX

The Source table here is not limited to a single table, but can also be the content of a subquery

typical example

INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE  = tbl_B.col);

The above SQL is for inserting into tbl_A the col that tbl_B contains, but tbl_A does not.
Changing to MERGE can be written as

MERGE INTO tbl_A  t  
    USING tbl_B v  
    ON  =   
    WHEN MATCHED THEN   
        UPDATE SET y.c2 = v.c2  
    WHEN NOT MATCHED THEN  
        INSERT (col, col2) VALUES (v.c1, v.c2);

(An extra UPDATE has been added here to show more options)
When a table needs to rely on another table for update operations, using MERGE can be a quick and easy way to achieve the