MERGE

SQL 入門 (DMLの基本形式:MERGE)

行の挿入と更新を１ステートメントで行う ( MERGE = UPDATE + INSERT )

行の挿入と更新を1ステートメントで行う。
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 だけでも書き換えが可能。しかし、副問い合わせを使用してすべてのリテラルを１ヵ所で記述することでリテラルの記述が重複したり、記述箇所を分散しないようにした方がわかりやすいだろう。
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'