MERGE文
MERGE構文といえば、通常はUPSERTと呼ばれる通り「あればUPDATEなければINSERT」という機能だが、DELETEも実装されている。Oracleでも10gからMERGEのDELETE句が実装されるようになったが、Oracleのとも少し違う。MERGEを使いたいケースというのは、たぶん以下の2つが代表的なものかと思う。1)差分データの更新 a) フラグなしで「あれば更新、なければ追加」 b)「追加/削除」のフラグ付の差分データを反映させる2)データの入れ替え (DELETE - INSERTでも実現できるが、影響のあるものだけ更新)データが以下の通りとすると、DECLARE @t1 TABLE ([F1][int],[F2][varchar](10))DECLARE @t2 TABLE ([MODE][varchar](1),[F1][int],[F2][varchar](10))INSERT INTO @t1 VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')INSERT INTO @t2 VALUES ('I',3,'X'),('D',5,'Y'),('I',6,'Z')SQL Server 2005までならば、以下のようにやっていた。1-a)UPDATE @t1 SET F2=t2.F2 FROM @t1 t1 INNER JOIN @t2 t2 ON t2.F1=t1.F1INSERT INTO @t1 SELECT F1,F2 FROM @t2 WHERE F1 NOT IN (SELECT F1 FROM @t1)1-b)DELETE FROM @t1 WHERE F1 IN (SELECT F1 FROM @t2 WHERE MODE='D')UPDATE @t1 SET F2=t2.F2 FROM @t1 t1 INNER JOIN @t2 t2 ON t2.F1=t1.F1 WHERE t2.MODE='I'INSERT INTO @t1 SELECT F1,F2 FROM @t2 WHERE MODE='I' AND F1 NOT IN (SELECT F1 FROM @t1);2)DELETE FROM @t1 WHERE F1 NOT IN (SELECT F1 FROM @t2);UPDATE @t1 SET F2=t2.F2 FROM @t1 t1 INNER JOIN @t2 t2 ON t2.F1=t1.F1;INSERT INTO @t1 SELECT F1,F2 FROM @t2 WHERE F1 NOT IN (SELECT F1 FROM @t1);それぞれ2または3クエリに分けて実装していたわけだが、MERGEを使うと以下の通り。WHEN MATCHED/WHEN NOT MATCHEDのほかに、WHEN NOT MATCHED BY SOURCEが準備されているのが特徴的で、これを使えば「なければ削除」が楽に指定できる。1-a)MERGE @t1 t1USING @t2 t2 ON t2.F1=t1.F1WHEN MATCHED THEN UPDATE SET F2=t2.F2WHEN NOT MATCHED THEN INSERT VALUES (t2.F1,t2.F2);1-b)MERGE @t1 t1USING @t2 t2 ON t2.F1=t1.F1WHEN MATCHED AND MODE='U' THEN UPDATE SET F2=t2.F2WHEN MATCHED AND MODE='D' THEN DELETE WHEN NOT MATCHED AND MODE='I' THEN INSERT VALUES (t2.F1,t2.F2);2)MERGE @t1 t1USING @t2 t2 ON t2.F1=t1.F1WHEN MATCHED THEN UPDATE SET F2=t2.F2WHEN NOT MATCHED THEN INSERT VALUES (t2.F1,t2.F2)WHEN NOT MATCHED BY SOURCE THEN DELETE;またOUTPUT句もサポートしているので、更新内容がINSERT/UPDATE/DELETEのいずれかも判断することができる。MERGE @t1 t1USING @t2 t2 ON t2.F1=t1.F1WHEN MATCHED THEN UPDATE SET F2=t2.F2WHEN NOT MATCHED THEN INSERT VALUES (t2.F1,t2.F2)OUTPUT $ACTION,inserted.F1,inserted.F2,deleted.F1,deleted.F2 INTO @F3;末尾の「;」は必須なので忘れないようにしたい。