MERGE文

OracleのSQLのMERGE文のメモ。

概要

MERGEは、データが既に存在していればUPDATE、無ければINSERTを行う構文。Oracle9iから使えるようになった。
データを取得元テーブルからSELECTし、更新対象テーブルに対してUPDATE/INSERTを行う。つまりデータを上書きコピーするようなイメージ。
UPDATEとINSERTを行うのでUPSERTと呼ばれることもあるらしい。

また、Oracle10gからは、UPDATE・INSERTそれぞれについてWHERE条件を指定できるようになった。
さらに、データを更新する以外に削除（DELETE）することも出来るようになった。

実のところ、UPDATE/INSERT実行時に取得元テーブルのレコードを削除する機能（つまりデータを移動するイメージ）があると とても便利だと思うのだが、今のところはそうした機能は無いようだ。

MERGE（UPDATE/INSERT）の例

例として、EMPからEMP_BKへデータをコピーしてみる。

--サンプル用テーブル作成
create table EMP_BK
as select * from EMP
where 1 = 0
;

--既存分のデータを用意
insert into EMP_BK (EMPNO, ENAME) values(7499, 'test');

select * from EMP_BK;

commit;

--データコピー
merge into EMP_BK t
using EMP f
on ( t.EMPNO = f.EMPNO )
when matched then
	update set
		t.ENAME    = f.ENAME,
		t.JOB      = f.JOB,
		t.MGR      = f.MGR,
		t.HIREDATE = f.HIREDATE,
		t.SAL      = f.SAL,
		t.COMM     = f.COMM,
		t.DEPTNO   = f.DEPTNO
when not matched then
	insert (t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO)
	values (f.EMPNO, f.ENAME, f.JOB, f.MGR, f.HIREDATE, f.SAL, f.COMM, f.DEPTNO)
;

commit;

into句で更新対象テーブルを指定する。
using句でデータ取得元テーブルを指定する。
on句でテーブルの結合条件を指定する。この結合条件によってマッチするデータがあった場合はwhen matched then updateによりデータ更新、マッチしなければwhen not matched then insertによりデータが追加される。
matchedブロックとnot matchedブロックのどちらを先に書いてもいい。
updateのsetやinsertのvaluesにはdefaultキーワードも指定可能。

mergeはupdateやinsertと同じく、最後にcommitする必要がある。

それにしてもinsertとupdateの構文って、データ（項目）の指定方法に統一感が無いなぁ(苦笑)

Oracle10gでの変更点

UPDATE・INSERTの省略

Oracle10gから、UPDATE・INSERTが省略可能になった。
（構文上は両方同時に省略可能っぽいが、さすがにそれは意味無いかｗ）

--存在しないデータだけコピー
merge into EMP_BK t
using EMP f
on ( t.EMPNO = f.EMPNO )
when not matched then
	insert (t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO)
	values (f.EMPNO, f.ENAME, f.JOB, f.MGR, f.HIREDATE, f.SAL, f.COMM, f.DEPTNO)
;

WHERE条件の指定例

Oracle10gから、UPDATE・INSERTそれぞれについてWHERE条件を指定できるようになった。

--WHERE条件を指定したデータコピー
merge into EMP_BK t
using EMP f
on ( t.EMPNO = f.EMPNO )
when matched then
	update set
		t.ENAME    = f.ENAME,
		t.JOB      = f.JOB,
		t.MGR      = f.MGR,
		t.HIREDATE = f.HIREDATE,
		t.SAL      = f.SAL,
		t.COMM     = f.COMM,
		t.DEPTNO   = f.DEPTNO
	where 7300 <= f.EMPNO and f.EMPNO < 7600
when not matched then
	insert (t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO)
	values (f.EMPNO, f.ENAME, f.JOB, f.MGR, f.HIREDATE, f.SAL, f.COMM, f.DEPTNO)
	where 7500 <= f.EMPNO and f.EMPNO < 7800
;

DELETEの例

Oracle10gから、UPDATEの際にさらに条件を指定してDELETEも出来るようになった。

merge into EMP_BK t
using EMP f
on ( t.EMPNO = f.EMPNO )
when matched then
	update set
		t.ENAME    = f.ENAME,
		t.JOB      = f.JOB,
		t.MGR      = f.MGR,
		t.HIREDATE = f.HIREDATE,
		t.SAL      = f.SAL,
		t.COMM     = f.COMM,
		t.DEPTNO   = f.DEPTNO
	where 7300 <= f.EMPNO and f.EMPNO < 7600
	delete
	where t.SAL < 1000
when not matched then
	insert (t.EMPNO, t.ENAME, t.JOB, t.MGR, t.HIREDATE, t.SAL, t.COMM, t.DEPTNO)
	values (f.EMPNO, f.ENAME, f.JOB, f.MGR, f.HIREDATE, f.SAL, f.COMM, f.DEPTNO)
	where 7300 <= f.EMPNO and f.EMPNO < 7600
;

deleteの条件に更新対象テーブルの項目を含む場合、その値はupdateによって更新された後のものになる。
つまり一旦updateによって更新が行われた後で、そのデータを削除するかどうか判定される。
（updateのwhere条件を「where 1 = 0」、つまり絶対更新されないようにしたら、deleteも全く行われない）