MERGE文、少し変わった使い方

time 2016/03/09

1回のSQLでデータ登録と更新を実現する便利なSQL文のMERGE文ですが、少し変わった使い方を本記事では紹介します。
oracle10gから出来るようになった使い方で、それ以降のバージョンで使用する事が出来ます。

存在する場合の更新のみ実施

MERGE文は、DML文の1つで、元テーブルのデータが先のテーブルに存在すれば更新、存在しなければ登録を1度に行うSQLですが、更新のみや登録のみを行うことが出来ます。

更新のみ行いたい場合は、WHEN MATCHEDのみを記述し、WHEN NOT MATCHEDを記述しないことで実現する事が出来ます。
他のテーブルの値でデータ更新する場合に、最適な実行計画で実現出来る方法の一つです。

-- 実行前
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001        
003 00003        

-- test_table2
A   B       C   D
--- ----- --- ---
001 00001 100 999
002 00002 200 888

MERGE INTO test_table1 t1
USING test_table2 t2
ON(    t1.a = t2.a
   AND t1.b = t2.b
  )
WHEN MATCHED THEN
  UPDATE SET c = t2.c
            ,d = t2.d
;

-- 実行後
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001 100 999 ← このデータが更新された
003 00003

-- 実行前

-- test_table1

A B C D

--- ----- --- ---

001 00001

003 00003

-- test_table2

A B C D

--- ----- --- ---

001 00001 100 999

002 00002 200 888

MERGE INTO test_table1 t1

USING test_table2 t2

ON( t1.a = t2.a

AND t1.b = t2.b

)

WHEN MATCHED THEN

UPDATE SET c = t2.c

,d = t2.d

;

-- 実行後

-- test_table1

A B C D

--- ----- --- ---

001 00001 100 999 ← このデータが更新された

003 00003

存在しない場合の登録のみ実施

登録のみ行いたい場合は、WHEN NOT MATCHEDのみを記述し、WHEN MATCHEDを記述しないことで実現する事が出来ます。

-- 実行前
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001        
003 00003        

-- test_table2
A   B       C   D
--- ----- --- ---
001 00001 100 999
002 00002 200 888

MERGE INTO test_table1 t1
USING test_table2 t2
ON(    t1.a = t2.a
   AND t1.b = t2.b
  )
WHEN NOT MATCHED THEN
  INSERT (a,b,c,d)
  VALUES (t2.a,t2.b,t2.c,t2.d)
;

-- 実行後
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001        
002 00002 200 888 ← このデータが登録された
003 00003

-- 実行前

-- test_table1

A B C D

--- ----- --- ---

001 00001

003 00003

-- test_table2

A B C D

--- ----- --- ---

001 00001 100 999

002 00002 200 888

MERGE INTO test_table1 t1

USING test_table2 t2

ON( t1.a = t2.a

AND t1.b = t2.b

)

WHEN NOT MATCHED THEN

INSERT (a,b,c,d)

VALUES (t2.a,t2.b,t2.c,t2.d)

;

-- 実行後

-- test_table1

A B C D

--- ----- --- ---

001 00001

002 00002 200 888 ← このデータが登録された

003 00003

更新や登録時に条件を指定する

更新や登録時に、条件を指定し特定のデータのみ更新や登録を行うことが出来ます。
UPDATEやINSERTの後ろにWHERE句を指定することで実現できます。

-- 実行前
-- test_table1
A   B       C   D
--- ----- --- ---
003 00003        
004 00004        

-- test_table2
A   B       C   D
--- ----- --- ---
001 00001 100 999
002 00002 200 888
003 00003 300 777
004 00004 400 666

MERGE INTO test_table1 t1
USING test_table2 t2
ON(    t1.a = t2.a
   AND t1.b = t2.b
  )
WHEN MATCHED THEN
  UPDATE SET c = t2.c
            ,d = t2.d
  WHERE t2.d = 777
WHEN NOT MATCHED THEN
  INSERT (a,b,c,d)
  VALUES (t2.a,t2.b,t2.c,t2.d)
  WHERE t2.d = 999
;

-- 実行後
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001 100 999 ← このデータが登録された
003 00003 300 777 ← このデータが更新された
004 00004

-- 実行前

-- test_table1

A B C D

--- ----- --- ---

003 00003

004 00004

-- test_table2

A B C D

--- ----- --- ---

001 00001 100 999

002 00002 200 888

003 00003 300 777

004 00004 400 666

MERGE INTO test_table1 t1

USING test_table2 t2

ON( t1.a = t2.a

AND t1.b = t2.b

)

WHEN MATCHED THEN

UPDATE SET c = t2.c

,d = t2.d

WHERE t2.d = 777

WHEN NOT MATCHED THEN

INSERT (a,b,c,d)

VALUES (t2.a,t2.b,t2.c,t2.d)

WHERE t2.d = 999

;

-- 実行後

-- test_table1

A B C D

--- ----- --- ---

001 00001 100 999 ← このデータが登録された

003 00003 300 777 ← このデータが更新された

004 00004

更新後、データを削除する

MERGE文のWHEN MATCHEDでデータを更新後、条件を指定しデータを削除する事も可能です。
例えば、論理削除されているデータは、更新先テーブルから削除するという使い方が考えられます。

WHEN MATCHEDのUPDATEの後に、DELETE WHERE 条件を記述します。更新後のデータの値で条件を評価します。

-- 実行前
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001        
002 00002        
003 00003        

-- test_table2
A   B       C   D
--- ----- --- ---
001 00001 100 999
002 00002 200 888
003 00003 300 777

MERGE INTO test_table1 t1
USING test_table2 t2
ON(    t1.a = t2.a
   AND t1.b = t2.b
  )
WHEN MATCHED THEN
  UPDATE SET c = t2.c
            ,d = t2.d
  DELETE
  WHERE  c = 300
;

-- 実行後
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001 100 999
002 00002 200 888
（更新後の項目cの値が300のデータが削除された）

-- 実行前

-- test_table1

A B C D

--- ----- --- ---

001 00001

002 00002

003 00003

-- test_table2

A B C D

--- ----- --- ---

001 00001 100 999

002 00002 200 888

003 00003 300 777

MERGE INTO test_table1 t1

USING test_table2 t2

ON( t1.a = t2.a

AND t1.b = t2.b

)

WHEN MATCHED THEN

UPDATE SET c = t2.c

,d = t2.d

DELETE

WHERE c = 300

;

-- 実行後

-- test_table1

A B C D

--- ----- --- ---

001 00001 100 999

002 00002 200 888

（更新後の項目cの値が300のデータが削除された）

注意点として、更新したデータのみ削除が有効ということです。

-- 実行前
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001 300    
002 00002 200    
003 00003 300    

-- test_table2
A   B       C   D
--- ----- --- ---
001 00001 100 999
002 00002 200 888
003 00003 300 777

MERGE INTO test_table1 t1
USING test_table2 t2
ON(    t1.a = t2.a
   AND t1.b = t2.b
  )
WHEN MATCHED THEN
  UPDATE SET c = t2.c
            ,d = t2.d
  WHERE  t2.d IN(888,777)
  DELETE
  WHERE  c = 300
;

-- 実行後
-- test_table1
A   B       C   D
--- ----- --- ---
001 00001 300     ← 更新されていないデータなので削除されない
002 00002 200 888