http://www.shift-the-oracle.com/sql/merge.html
SQL 入門 (DMLの基本形式:MERGE)
行の挿入と更新を 1ステートメントで行なう ( MERGE = UPDATE + INSERT )
行の挿入と更新を1ステートメントで行なう。他の DBMS では REPLACE の場合もある。
UPDATE と INSERT をもじって、UPSERT とも呼ばれる。(モデルファンクション内でも UPSERT のキーワードがある)
テーブルからテーブルにデータをエントリー(または更新)する
人事システムから最新のマスタ (USER_MASTER_IMPORT) を取得してきた。(と仮定する)
これを USER_MASTER に最新として反映させる。新規レコードには、名前に'(新人)'を付与する。MERGE INTO USER_MASTER USING USER_MASTER_IMPORT ON ( USER_MASTER.USER_ID = USER_MASTER_IMPORT.USER_ID) -- 既存レコードの更新 WHEN MATCHED THEN UPDATE SET USER_NAME = USER_MASTER_IMPORT.USER_NAME, DEPT_NO = USER_MASTER_IMPORT.DEPT_NO, MODIFIED_ON = SYSDATE -- 新規レコードの作成 WHEN NOT MATCHED THEN INSERT ( USER_ID, USER_NAME, DEPT_NO ) VALUES ( USER_MASTER_IMPORT.USER_ID, USER_MASTER_IMPORT.USER_NAME || '(新人)', USER_MASTER_IMPORT.DEPT_NO )
- 基本的なフォーマット
MERGE INTO <表名1 [エイリアス名] USING 表名2 | 副問い合わせ [エイリアス名] ON ( <結合条件> ) WHEN MATCHED THEN UPDATE SET <カラム名> = < 値 >, … WHEN NOT MATCHED THEN INSERT [ (<カラム名>, … ) ] VALUES ( <値> , … ) ;
MERGE の情報ソースにテーブルでなくリテラルを使用する
入力されるデータがテーブルではなくユーザー入力の リテラル 指定などによるデータの集まりの場合には DUAL 表 を使用した仮想テーブルにすることで INSERT と UPDATE の条件分岐が可能。
副問い合わせでなく DUAL だけでも書き換えが可能。しかし、副問い合わせを使用してすべてのリテラルを1ヵ所で記述することでリテラルの記述が重複したり、記述箇所を分散しないようにした方がわかりやすいだろう。MERGE INTO USER_MASTER USING ( SELECT '0099' "USER_ID", '入力した名前' "USER_NAME", '0010' "DEPT_NO" FROM DUAL ) phantom ON (USER_MASTER.USER_ID = phantom.USER_ID) -- または -- USING DUAL ON (USER_MASTER.USER_ID = '0099') -- -- 既存レコードの更新 WHEN MATCHED THEN UPDATE SET USER_NAME = USER_NAME || '(更新)', MODIFIED_ON = SYSDATE -- 新規レコードの作成 WHEN NOT MATCHED THEN INSERT ( USER_ID, USER_NAME, DEPT_NO ) VALUES (phantom.USER_ID, -- or '0099' phantom.USER_NAME,-- or '入力した名前' phantom.DEPT_NO ) -- or '0010'