MERGE の紹介

SQL Server

MERGE は SQL Server 2008 から追加されました。
MERGE (Transact-SQL)

更新対象テーブルと元となるテーブルの結合結果に対し、更新処理を行います。
更新対象テーブルA に テーブルB を結合して、A に無く B にある場合は INSERT、両方にある場合、UPDATE、A にあって B に無い場合は、DELETE ということが 1つの SQL 文で実現出来ます。
A と B を結合した際の、データの有無

INSERT UPDATE DELETE
A 無 有 有
B 有 有 無

クエリを見ながら解説していきます。今回は、マスタとスレイブの2テーブルがあり、マスタの内容をスレイブに同期させるシナリオとします。

use [テスト]
-- 準備
create table [マスタ] ( 
  [Id] int identity(1,1) not null primary key, 
  [名前] nvarchar(10) not null 
)
create table [スレイブ] ( 
  [Id] int not null primary key, 
  [名前] nvarchar(10) not null 
)
go
insert into [マスタ] values ('さとう'), ('たなか'), ('すずき')
insert into [スレイブ] values ( 0, '削除されるはず' ), ( 1, '更新されるはず' )
-- データ確認
select 'マスタ', [Id], [名前] from [マスタ]
select 'スレイブ', [Id], [名前]  from [スレイブ]
go


テーブルを作成して、テストデータを準備します。

-- Merge を使わないパターン
begin tran

update [スレイブ] set [名前] = [src].[名前] 
from [マスタ] as [src] 
where [スレイブ].[Id] = [src].[Id]

insert into [スレイブ] 
select * 
from [マスタ] as [src] 
where not exists ( select [Id] from [スレイブ] as [dest] where [dest].[Id] = [src].[Id] )

delete from [スレイブ] 
where not exists ( select [Id] from [マスタ] as [src] where [スレイブ].[Id] = [src].[Id] )
-- データ確認
select 'スレイブ', [Id], [名前]  from [スレイブ]

rollback -- 次の MERGE でデータを更新するので今回はロールバックする
go


似たような条件で、3つのクエリ(INSERT/UPDATE/DELETE)を発行する必要があります。これが MERGE を使うことで 1つのクエリになります。

-- Merge
merge [スレイブ] as [dest] 
using ( select [Id], [名前] from [マスタ] ) as [src] on ( [dest].[Id] = [src].[Id] )
when matched then update set [名前] = [src].[名前] 
when not matched by target then insert ( [Id], [名前] ) values ( [src].[Id], [src].[名前] )
when not matched by source then delete;
-- データ確認
select 'マスタ', [Id], [名前] from [マスタ]
select 'スレイブ', [Id], [名前]  from [スレイブ]
go


MERGE は必ず最後に ";"(セミコロン) で終わる必要があります。
上記クエリは、[Id] で マスタとスレイブを結合し、スレイブにデータが無い場合は INSERT、マスタ/スレイブ 共にデータがある場合は UPDATE、マスタにデータが無い場合は DELETE を発行する MERGE を実行しています。
T-SQL の　MERGE は結構柔軟で データの有無により 3つのパターンに分かれます。

  • WHEN MATCHED THEN ： 共にデータが存在している場合に実行される。UPDATE/DELETE を実行することが出来る。
  • WHEN NOT MATCHED BY TARGET THEN ： 更新対象にデータが存在せず、結合元にはデータが存在する場合に実行される。INSERT を実行することが出来る。
  • WHEN NOT MATCHED BY SOURCE THEN ： 更新対象にデータが存在し、結合元にはデータが存在しない場合に実行される。UPDATE/DELETE を実行することが出来る。


次は OUTPUT 句 の紹介 - お だ のスペース で紹介した OUTPUT 句も使ってみましょう。

-- マスタのデータ変更
insert into [マスタ] values ( 'あべ' )
update [マスタ] set [名前] = '旧姓たなか' where [名前] = 'たなか'
delete from [マスタ] where [名前] = 'すずき'

-- Merge - output
begin tran

merge [スレイブ] as [dest] 
using [マスタ] as [src] on ( [dest].[Id] = [src].[Id] )
when matched then update set [名前] = [src].[名前] 
when not matched by target then insert ( [Id], [名前] ) values ( [src].[Id], [src].[名前] )
when not matched by source then delete
output deleted.*, $action, inserted.*;

rollback -- 次も同じデータを使うので今回はロールバックする
go


MERGE で OUTPUT 句を利用する場合に、$action という特殊な列を指定する事が出来ます。これは実行されたクエリに従って 'INSERT', 'UPDATE', 'DELETE' のいずれかの値が返ってきます。
この実行結果を見てみると、名前が変わっていない行も UPDATE されています。追加の条件を指定することで、こういった無駄な更新処理を省くことが出来ます。

-- Merge - output (無駄な更新を省く)
merge [スレイブ] as [dest] 
using ( select [Id], [名前] from [マスタ] ) as [src] on ( [dest].[Id] = [src].[Id] )
when matched and [dest].[名前] <> [src].[名前] then update set [名前] = [src].[名前] 
when not matched by target then insert ( [Id], [名前] ) values ( [src].[Id], [src].[名前] )
when not matched by source then delete
output deleted.*, $action, inserted.*;
go


WHEN MATCHED の後に、AND 〜 で条件を追加しています。これは マスタテーブルとスレイブテーブルの結合条件では無く、「更新するデータを絞り込むための条件」です。
WHEN MATCHED だけではなく、WHEN NOT MATCHED BY TARGET / WHEN NET MATCHED BY SOURCE の後にも同様に条件を追加することが出来ます。


MERGE を使うことで、今まで複数回クエリを発行していたクエリをシンプルに書くことが出来ます。また元となるテーブル(USING で指定しているテーブル)は、サブクエリ等も利用可能なので複雑なケースにも対応出来ると思います。
MERGE が利用出来るケースでは MERGE を使っていきましょう！

クエリ全文

use [テスト]
-- 準備
create table [マスタ] ( 
  [Id] int identity(1,1) not null primary key, 
  [名前] nvarchar(10) not null 
)
create table [スレイブ] ( 
  [Id] int not null primary key, 
  [名前] nvarchar(10) not null 
)
go
insert into [マスタ] values ('さとう'), ('たなか'), ('すずき')
insert into [スレイブ] values ( 0, '削除されるはず' ), ( 1, '更新されるはず' )
-- データ確認
select 'マスタ', [Id], [名前] from [マスタ]
select 'スレイブ', [Id], [名前]  from [スレイブ]
go
-- Merge を使わない
begin tran
update [スレイブ] set [名前] = [src].[名前] 
from [マスタ] as [src] 
where [スレイブ].[Id] = [src].[Id]
insert into [スレイブ] 
select * 
from [マスタ] as [src] 
where not exists ( select [Id] from [スレイブ] as [dest] where [dest].[Id] = [src].[Id] )
delete from [スレイブ] 
where not exists ( select [Id] from [マスタ] as [src] where [スレイブ].[Id] = [src].[Id] )
select 'スレイブ', [Id], [名前]  from [スレイブ]
rollback -- 次の MERGE でデータを更新するので今回はロールバックする
go
-- Merge
merge [スレイブ] as [dest] 
using ( select [Id], [名前] from [マスタ] ) as [src] on ( [dest].[Id] = [src].[Id] )
when matched then update set [名前] = [src].[名前] 
when not matched by target then insert ( [Id], [名前] ) values ( [src].[Id], [src].[名前] )
when not matched by source then delete;
-- データ確認
select 'マスタ', [Id], [名前] from [マスタ]
select 'スレイブ', [Id], [名前]  from [スレイブ]
go

-- マスタのデータ変更
insert into [マスタ] values ( 'あべ' )
update [マスタ] set [名前] = '旧姓たなか' where [名前] = 'たなか'
delete from [マスタ] where [名前] = 'すずき'
begin tran
-- Merge - output
merge [スレイブ] as [dest] 
using ( select [Id], [名前] from [マスタ] ) as [src] on ( [dest].[Id] = [src].[Id] )
when matched /*and [dest].[名前] != [src].[名前] */then update set [名前] = [src].[名前] 
when not matched by target /* [src].[名前] */ then insert ( [Id], [名前] ) values ( [src].[Id], [src].[名前] )
when not matched by source /* [dest].[名前] */ then delete
output deleted.*, $action, inserted.*;
rollback -- 次の MERGE でデータを更新するので今回はロールバックする
go
-- Merge - output (無駄な更新を省く)
merge [スレイブ] as [dest] 
using ( select [Id], [名前] from [マスタ] ) as [src] on ( [dest].[Id] = [src].[Id] )
when matched and [dest].[名前] <> [src].[名前] then update set [名前] = [src].[名前] 
when not matched by target then insert ( [Id], [名前] ) values ( [src].[Id], [src].[名前] )
when not matched by source then delete
output deleted.*, $action, inserted.*;
go
-- 後片付け
drop table [マスタ]
drop table [スレイブ]
go