MERGE文、エラーになるケース3つ

time 2016/03/16

ORACLEのMERGE文、1回のSQLでデータ登録と更新を実現する便利なSQL文ですが、気をつけないと実行エラーとなることがあります。
本記事では、MERGE文のエラーになるケース3つを紹介します。

ON句で指定した項目については、更新が出来ない

ON句で指定した項目については、更新が出来ません。

ON句で指定した項目を更新しようとすると、エラー（ORA-38104）が発生します。

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

ORA-38104: ON句で参照する列は更新できません: "T1"."B"

MERGE INTO test_table1 t1

USING test_table2 t2

ON( t1.a = t2.a

AND t1.b = t2.b

)

WHEN MATCHED THEN

UPDATE SET b = t2.e

,c = t2.c

,d = t2.d

;

ORA-38104: ON句で参照する列は更新できません: "T1"."B"

マージ元テーブルに同一キーのデータが複数（データ登録時）

マージ先テーブルに同一キーのデータが存在しない場合、MERGE文ではデータ登録を行います。
その場合でマージ元テーブルに同一キーのデータが複数存在した場合で、かつ、マージ先テーブルに主キーや一意キーが設定されている場合、キー重複エラー（ORA-00001）が発生します。

1件目でデータ登録、2件目でデータを更新というような動作はしません。（そもそもどちらを最終値とするか、という問題になるため、エラーとして落とした方が分かりやすいので、よい動作かと個人的には思います）

-- test_table1（項目A＋項目Bが主キー）
A   B       C   D
--- ----- --- ---
001 00001        
 
-- test_table2
A   B       C   D
--- ----- --- ---
002 00002 200 888
002 00002 300 777
 
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)
;

ORA-00001: 一意制約(zzzzz.yyyyy)に反しています

zzzzz：スキーマ名
yyyyy：主キー名・一意キー名

-- test_table1（項目A＋項目Bが主キー）

A B C D

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

001 00001

-- test_table2

A B C D

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

002 00002 200 888

002 00002 300 777

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)

;

ORA-00001: 一意制約(zzzzz.yyyyy)に反しています

zzzzz：スキーマ名

yyyyy：主キー名・一意キー名

マージ元テーブルに同一キーのデータが複数（データ更新時）

マージ先テーブルに同一キーのデータが存在する場合、MERGE文ではデータ更新を行います。
その場合でマージ元テーブルに同一キーのデータが複数存在した場合、エラー（ORA-30926）が発生します。

それぞれのデータで、2回更新というような動作はしません。（これについても、そもそもどちらを最終値とするか、という問題になるため、エラーとして落とした方が分かりやすいので、よい動作かと個人的には思います）

-- test_table1（項目A＋項目Bが主キー）
A   B       C   D
--- ----- --- ---
002 00002        
 
-- test_table2
A   B       C   D
--- ----- --- ---
002 00002 200 888
002 00002 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
;

ORA-30926: ソース表の安定したセット行を取得できません