【UPSERTだけじゃない!】SQLのMERGE句の使い方を丁寧に解説【初心者向け】

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください
【6/19開催】Kong Community Japan Meetup #4
本イベントでは、Kong Inc. のVP of ProductであるReza Shafii氏もプレゼンターとして参加。当社からはアーキテクト マネージャーの槌野の登壇が決定!参加無料です!!
https://column.api-ecosystem.sios.jp/connect/kong/1081/

【6/21開催】開発者目線でのSBOMとの向き合い方
SBOMの導入から開発者がSBOMの作成・管理を自動で行っていくための方法(デモ)を紹介します。SBOMを全く知らない人から、開発との統合までを紹介するので様々なレベルの方に学びがあるライブとなる予定です!
https://tech-lab.connpass.com/event/321422/

【7/19開催】現場で役立つAzure神小技10+α 〜生成AI,RAG,コスト削減など旬な技術満載のLT大会〜
Azureの最新技術や実用的な小技を紹介する特別なライトニングトーク大会を開催します!
https://tech-lab.connpass.com/event/319077/

【7/26開催】最適なIaCツールを選ぼう
プロジェクトでのツール選びに困らないための重要な観点をご説明します!
https://tech-lab.connpass.com/event/319532/

こんにちは、サイオステクノロジーの佐藤 陽です。

今回は、T-SQLなどで使われるMERGE句について紹介したいと思います。
追加・削除・更新が一気に行える便利なSQL句なので是非使いこなしていきましょう!

  • 「DBに存在しなかったら新規追加、存在していたら更新」という処理を一度に行いたい
  • MERGE句のドキュメント見たけどよく分からない

といった方は是非最後までご覧ください!

はじめに

今回はSQLServerを題材に、MERGE句についての基本的な使い方をご紹介をします。
とても便利なSQL句なのですが、若干の分かりづらさがるので図を交えて分かりやすく解説していきます。

まず、MERGE句はT-SQLで提供されているステートメントの一つになります。

よくある例として

「追加したいレコードが存在しなければ追加、存在していれば更新」

といったような、UPSERTと呼ばれる処理を行う際に、利用されることも多いのではないでしょうか?

自分もこの UPSERT の処理を行いたくて色々調べているうちに、「MERGEを使えばいいよ」という情報にたどり着きました。

ただそういう訳もあって、はじめは UPSERT の処理と MERGEという句の名前 のギャップに少し戸惑いました。

  • なんでMERGEって名前?UPSERTでいいじゃん。
  • よくわからないパラメータもたくさんある…

なんて思っていましたが、

調べていくうちにMERGE句はUPSERTだけではなく、もう少し踏み込んだ処理を行うことができることが分かり
このMERGEという命名も非常にも納得できるようになりました。

そのため本記事では、「UPSERTだけじゃない!MERGE句の全体像!」をサブタイトルとして、今回は紹介していきたいと思います。

MERGEのイメージ

ソフトウェアエンジニアの人が「MERGE」を聞いたときに思い浮かべるのは
GitやMercurialなどのバージョン管理ツールでの言葉ではないでしょうか?

例えばdevelopブランチを生やして作業し、それをmasterブランチに戻すときに「マージする」といいますもんね。

そしてこの時、もしConflictが発生したら以下のような手段を使って解消しますよね

  • master(target)の内容を採用する
  • Source(source)の内容を採用する
  • 両方採用する
    etc.

SQLのMERGE句もまさにこのイメージに近いのです!

(ここが個人的に一番重要な理解です)

上の例のGitの場合と、SQLの場合を対応させると

master(target)既にデータベースに存在するデータ
develop(source)新しく追加・更新したいデータ

となります。

図に表すと以下のような形です。

ここで、TargetとSourceデータに関して、idの値を基準として比較を行ってみます。
※分かりづらいので明記しておきますがsourceのテーブルにおいてMichaelさんのemailが変更されています。

 

(表示の都合上、idを上位8桁のみ、またemailのカラムを省略しています。)

すると、

  1. 同じidデータのレコードが存在する
  2. Sourceには存在するが、Targetには存在しない
  3. Targetには存在するが、Sourceには存在しない

の3パターンに分類できることが分かります。

MERGE句では、この3パターンのレコードに対してそれぞれ別の処理を行うことができます。

例えば、以下のような感じです。

  1. 同じidデータのレコードが存在する → 更新処理
  2. Sourceには存在するが、Targetには存在しない → 新規追加
  3. Targetには存在するが、Sourceには存在しない → 削除処理

UPSERTという面だけ見れば、1, 2だけの機能なのですが
MERGE句は3.の機能も持ってるため、MERGEという名前を冠していると思われます。

そして、この1.~3.がMERGE句の持つ以下のパラメータに対応しています。

  1. WHEN MATCHED THEN
  2. WHEN NOT MATCHED [ BY TARGET ] THEN
  3. WHEN NOT MATCHED BY SOURCE THEN

このWHEN~以下に、適切な処理を記載することで、一度のクエリ実行で3パターンの処理を適用することができます。

ここまで分かれば実際に実装してみましょう!

実装

データベースの準備部分はに関する詳細は省略します。

今回はAzure上にSQLDatabaseを構築し、そこにテーブルを作成していきます。

サンプルデータ

サンプルデータを用意します。

今回はChatGPTにサンプルデータの作成を依頼しました。
こういう時すごい便利ですねー。

プロンプト
###
あなたはソフトウェアエンジニアです。
SQLServerに以下情報を持つテーブルを作成し、サンプルデータを2件追加したいです。
idの値としてはGUIDの値を持つものとします。
サンプルのSQLを提供してください。
テーブル名:users 
カラム:id, name, email

得られた回答がこちら。

SQL ServerでGUIDを使用してテーブルを作成し、サンプルデータを2件追加するSQL文は以下の通りです。
-- テーブルを作成
CREATE TABLE users (
    id UNIQUEIDENTIFIER PRIMARY KEY,
    name NVARCHAR(255),
    email NVARCHAR(255)
);

-- サンプルデータを2件追加
INSERT INTO users (id, name, email)
VALUES
    (NEWID(), 'Jane Smith', 'jane.smith@example.com'),
    (NEWID(), 'Michael Johnson', 'michael.johnson@example.com');

では作ってもらったSQLを実行してみます。
(※CREATE TABLEのテーブル名にschemaが含まれてなかったので dbo.users と修正し、実行しました。)

作成されました。

ではこれに対してMERGE句を実行していきます。

今回、新規追加・更新したいデータは上の図で示したものと同じ、以下2件です。

b13bde46-6bda-45dc-abde-9b2768f9fc3cMichael Johnsonmichael.johnson2@example.com
7b21681a-f075-41ff-74ad-449cfbd17261 Emily Davis emily.davis@example.com

Michaelさんのメールアドレスがsourceとtargetで異なっているので、更新される点に注目ですね。

ではクエリを組んでいきます。

merge into users as target /** targetとなるテーブルを指定 **/
using ( /** sourceとなるテーブルを指定. **/
    values
        (convert(uniqueidentifier,'b13bde46-6bda-45dc-abde-9b2768f9fc3c'), 'Michael Johnson', 'michael.johnson2@example.com'),
        (convert(uniqueidentifier,'7b21681a-f075-41ff-74ad-449cfbd17261'), 'Emily Davis', 'emily.davis@example.com')
) as source (id, name, email)
on target.id = source.id /** targetとsourceの比較を行う条件を指定. 今回はidの値で比較 **/
when matched then /** target, sourceどちらも存在する(一致するものがある)場合は更新 **/
    update set target.name = source.name, /** nameを更新 **/
    target.email = source.email  /** emailを更新 **/
when not matched by target then /**sourceにのみ存在する場合は新規追加**/
    insert (id, name, email) /** 新規追加 **/
    values (source.id, source.name, source.email)
when not matched by source then /**targetのみに存在する場合は、targetから削除**/
    delete; /** 削除 **/


/** 実行後のデータ表示 */
select [id]
      , [name]
      , [email]
from [dbo].[users]

実行してみます。

 

それぞれ「新規追加」・「更新」・「削除」の処理が行われていることが分かります。

今回は1行ずつの処理でしたが、もちろん複数レコードに対しても処理ができます。
また、今回比較の条件をidのみとしましたが、複数カラムを比較条件にすることも可能です。

ただ複雑なことを行おうとすると、想定しないdeleteが行われがちなので
そのあたり注意してSQLを組んでみてください。

このあたりにも注意事項が書いてありますね。

照合目的で使用する対象テーブルの列だけを指定することが重要です。 つまり、対象テーブルのうち、ソース テーブルの対応する列と比較する列を指定します。 AND NOT target_table.column_x = value と指定するなど、ON 句で対象テーブル内の行にフィルターを適用することによって、クエリ パフォーマンスを向上させようとしないでください。 この場合、予期しない無効な結果が返されることがあります。

まとめ

今回はT-SQLのMERGE句の概要と使い方を紹介しました。
UPSERTに限った処理ではなく、2つのテーブルを比較し、その比較結果によって別の処理を振り分けることができます。
非常に便利なSQL句だと思うので、是非使ってみてください。

ではまた!

アバター画像
About 佐藤 陽 57 Articles
ロードバイクやランニングなど、走ることが好きなサーバーサイドエンジニア。組み込み系からWeb系へとジョブチェンジし、現在はAzureを使ったWebアプリの開発や、DevOpsの構築を行っています。
ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。


ご覧いただきありがとうございます。
ブログの最新情報はSNSでも発信しております。
ぜひTwitterのフォロー&Facebookページにいいねをお願い致します!



>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


質問はこちら 閉じる