---

MERGE INTO

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

CREATE TABLE A2
(
  CH  VARCHAR2(5 BYTE),
  DT  INTEGER,
  YY  VARCHAR2(2 BYTE)
)
TABLESPACE USERS
LOGGING
NOCACHE
NOPARALLEL;

 

Insert into A2
   (CH, DT, YY)
 Values
   ('1', 2, 'B');
Insert into A2
   (CH, DT, YY)
 Values
   ('3', 5, 'C');
Insert into A2
   (CH, DT, YY)
 Values
   ('4', 5, 'A');
Insert into A2
   (CH, DT, YY)
 Values
   ('5', 5, 'A');
COMMIT;

CREATE TABLE A1
(
  CH  VARCHAR2(5 BYTE),
  DT  INTEGER,
  YY  VARCHAR2(2 BYTE)
)
TABLESPACE USERS
LOGGING
NOCACHE
NOPARALLEL;

Insert into A2
   (CH, DT, YY)
 Values
   ('1', 2, 'B');
Insert into A1
   (CH, DT, YY)
 Values
   ('3', 1, 'C');
Insert into A1
   (CH, DT, YY)
 Values
   ('4', 9, 'A');
Insert into A1
   (CH, DT, YY)
 Values
   ('7', 9, 'B');
Insert into A1
   (CH, DT, YY)
 Values
   ('5', 8, 'A');
COMMIT;
________________________________________
SQL> SELECT * FROM A1;

CH            DT YY
----- ---------- --
3              1 C
1              2 B
4              9 A
7              9 B
5              8 A

SQL> SELECT * FROM A2;

CH            DT YY
----- ---------- --
1              2 B
3              5 C
4              5 A
5              5 A
________________________________________
MERGE INTO --- UPDATE

1>
SQL> MERGE INTO A2 A2
  2        USING A1 A1
  3        ON (A2.CH = A1.CH AND A1.YY = A2.YY)
  4        WHEN MATCHED THEN
  5        UPDATE
  6        SET A2.DT = A1.DT;

4 rows merged.

2>

SQL> SELECT * FROM A2;

CH            DT YY
----- ---------- --
1              2 B
3              1 C
4              9 A
5              8 A

3>
SQL> ROLLBACK;

Rollback complete.

________________________________________
MERGE INTO --- UPDATE + DELETE
1>
SQL> MERGE INTO A2 A2
  2        USING A1 A1
  3        ON (A2.CH = A1.CH AND A1.YY = A2.YY)
  4        WHEN MATCHED THEN
  5        UPDATE
  6        SET A2.DT = A1.DT
  7        DELETE WHERE A2.DT=2;

4 rows merged.

2>
SQL> SELECT * FROM A2;

CH            DT YY
----- ---------- --
3              1 C
4              9 A
5              8 A

3>
SQL>  ROLLBACK;

Rollback complete.

________________________________________

MERGE ---UPDATE+DELETE+INSERT

1>
SQL> MERGE INTO A2 A2
  2        USING A1 A1
  3        ON (A2.CH = A1.CH AND A1.YY = A2.YY)
  4        WHEN MATCHED THEN
  5        UPDATE
  6        SET A2.DT = A1.DT
  7        DELETE WHERE A2.DT=2
  8        WHEN NOT MATCHED THEN
  9        INSERT
 10        VALUES (A1.CH,A1.DT,A1.YY);

5 rows merged.

2>
SQL> SELECT * FROM A2;

CH            DT YY
----- ---------- --
3              1 C
7              9 B
4              9 A
5              8 A

3>

SQL> ROLLBACK;

Rollback complete.

 

________________________________________

MERGE INTO 自更新

 

1>

SQL>  MERGE INTO A2 A2
  2       USING (SELECT * FROM A2) A1
  3        ON (A2.CH = A1.DT AND A2.CH = A1.CH)
  4       WHEN MATCHED THEN
  5       UPDATE
  6       SET A2.DT = A2.DT + 1;

1 row merged.

 

2>

SQL> SELECT * FROM A2;

CH            DT YY
----- ---------- --
1              2 B
3              5 C
4              5 A
5              6 A