OracleのMERGEの使い方 - hogehoge foobar Blog Style Beta

OracleでINSERT/UPDATEの処理を書くのってなかなか面倒なので、なにか良い方法は無いかと探してみたらMERGEって方法がありました。
何気に便利だったので、覚え書きとして書いておきます。

※MERGEはOracleでしか使えないようです。

MERGEの基本構文

MERGE INTO データ投入先のテーブル名
  USING データ元のテーブル名
  ON (条件式) 
  WHEN MATCHED THEN 
    UPDATE SET
     UPDATEする項目
  WHEN NOT MATCHED THEN
    INSERT (INSERT対象項目)
    VALUES (INSERT対象データ)

同一構造のテーブルを元にMERGE

同一構造のテーブルを元データとしてMERGEする場合は、

USING句に「元データのテーブル名」

を記述します。

SQL文

MERGE INTO foo
  USING bar 
  ON (foo.id = bar.id) 
  WHEN MATCHED THEN
    UPDATE SET 
      foo.name = bar.name,
      foo.age = bar.age
  WHEN NOT MATCHED THEN
    INSERT (id, name, age)
    VALUES (bar.id, bar.name, bar.age)

MERGE後のテーブル結果

foo

ID	NAME	AGE
001	トーマス	10
002	ゴードン	20

bar

ID	NAME	AGE
002	ゴードン	25
003	ウィリアム	30

SQL実行後のfoo

ID	NAME	AGE
001	トーマス	10
002	ゴードン	25
003	ウィリアム	30

副問い合わせの結果を元にMERGE

副問い合わせの結果を元データとしてMERGEする場合は、

USING句に「 (副問い合わせSQL文) テーブル別名」

を記述します。

SQL文

MERGE INTO foo
  USING 
  (select * from bar where id='002') bar 
  ON (foo.id = bar.id) 
  WHEN MATCHED THEN
    UPDATE SET 
      foo.name = bar.name,
      foo.age = bar.age
  WHEN NOT MATCHED THEN
    INSERT (id, name, age)
    VALUES (bar.id, bar.name, bar.age)

MERGE後のテーブル結果

副問い合わせbarテーブルのid='002'のデータのみを指定しているため、SQL実行後もid='003'のレコードは追加されません。

foo

ID	NAME	AGE
001	トーマス	10
002	ゴードン	20

bar

ID	NAME	AGE
002	ゴードン	25
003	ウィリアム	30

SQL実行後のfoo

ID	NAME	AGE
001	トーマス	10
002	ゴードン	25

※今回は簡単な説明のため同一構造テーブルから条件指定のみをしていますが、実際は

ユーザー指定のデータ元にMERGE

ユーザー指定のデータ(アプリケーションの入力値など)を元データとしてMERGEする場合は、

USING句に「 ( select 'データ' AS 別名 from dual ) テーブル別名」

を記述します。

SQL文

MERGE INTO foo
  USING 
  (select 
    '00004' AS id,
    'ウェンディ' AS name,
    '25' AS age
   from dual) bar 
  ON (foo.id = bar.id) 
  WHEN MATCHED THEN
    UPDATE SET 
      foo.name = bar.name,
      foo.age = bar.age
  WHEN NOT MATCHED THEN
    INSERT (id, name, age)
    VALUES (bar.id, bar.name, bar.age)

MERGE後のテーブル結果

foo

ID	NAME	AGE
001	トーマス	10
002	ゴードン	20

SQL実行後のfoo

ID	NAME	AGE
001	トーマス	10
002	ゴードン	20
004	ウェンディ	25

ON句で指定した項目は更新できない

MERGEを使用する場合、ON句で指定した項目はUPDATEする事が出来ません。
間違ってUPDATEしようとするとORACLEエラー(ORA-38104)になります。

SQL文

MERGE INTO foo
  USING bar 
  ON (foo.id = bar.id) 
  WHEN MATCHED THEN
    UPDATE SET 
      foo.id = bar.id,   /* → ON句で指定した項目をUPDATEしようとしている */
      foo.name = bar.name,
      foo.age = bar.age
  WHEN NOT MATCHED THEN
    INSERT (id, name, age)
    VALUES (bar.id, bar.name, bar.age)

Oracleエラーの内容

ORA-38104: ON句で参照する列は更新できません: string
原因: UPDATE SETの選択述語左辺(LHS)に、ON句で参照される列があります。
処置: なし
http://otndnld.oracle.co.jp/document/products/oracle10g/101/doc_v12/server.101/B12448-02/e38001.htm

MERGEって便利

PHPだったりJavaだったりのアプリケーション側からINSERT/UPDATEする場合って、

最初にINSERT
重複エラーだったらUPDATE

みたいなことをしていましたが、MERGEなら、

最初にMERGE

だけで完結してくれます。

今回参考にしたページ

忘れっぽいエンジニアのオラクルＳＱＬリファレンス（MERGE）
http://oracle.se-free.com/dml/09_merge.html

Oracle10gでSQL - MERGE - [データベース] All About
http://allabout.co.jp/gm/gc/47410/