Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ Teradata Syntax Migration/ Data Manipulation Language (DML)/ MERGE
Updated on 2024-07-19 GMT+08:00
View PDF
Share

MERGE

MERGE is an ANSI-standard SQL syntax operator used to select rows from one or more sources for updating or inserting into a table or view. The conditions to update or insert to the target table or view can be specified.

Input: MERGE

1
2
3
4
5
6
7
8
 
MERGE INTO tab1 A
using ( SELECT c1, c2, ... FROM tab2 WHERE ...) AS B
ON A.c1 = B.c1
 WHEN MATCHED THEN 
   UPDATE SET c2 = c2
            , c3 = c3
  WHEN NOT MATCHED THEN 
INSERT VALUES (B.c1, B.c2, B.c3);

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
WITH B AS (
     SELECT
               c1
               ,c2
               ,...
          FROM
               tab2
          WHERE
               ...
)
,UPD_REC AS (
     UPDATE
               tab1 A
          SET
               c2 = c2
               ,c3 = c3
          FROM
               B
          WHERE
               A.c1 = B.c1 returning A. *
)
INSERT
     INTO
          tab1 SELECT
                    B.c1
                    ,B.c2
                    ,B.c3
                 FROM
                    B
                WHERE
                    NOT EXISTS (
                         SELECT
                                 1
                           FROM
                                 UPD_REC A
                          WHERE
                                 A.c1 = B.c1
                               )
;