この記事は、Upsertについての概要と、主なDBで具体的にどう記述するかをザックリ知りたい方向けの記事になっています。
主なDBとして Oracle,SQL Server,SQL Database,PostgreSQL,MySQL,SQLiteについて取り扱っており、それぞれ具体的なSQLを記載しているので、辞書的な使い方もできるかと思います。
UPSERTとは
「INSERT文を実行する際、すでにデータが存在すれば UPDATEを実行し、なければそのままINSERT文を実行する」という動作を意味します。
通常はテーブル間で同期をとりたい場合に用いますが、複数人数でデータを同時更新するような場合にも利用されます。
これから更新しようとするデータが、対象テーブルに存在するか分かっていれば、UPSERTは必要ありません。しかし、プログラムやシステムの要件によっては分からないケースもあります。
以前は、事前にSELECTして存在を確認してからINSERTするか、あるいは必ずDELETEしてからINSERTするという方法が多く用いられていました。
これでも要件は満たせはしますが、SQL文を２回発行する必要があるため処理速度が遅くなりますし、対象テーブルに更新日時があった場合、DELETEと共に消滅してしまうという問題も発生します。
そこで、各社のDBメーカーは機能拡張を行い、今では多くのDBでUPSERTが利用できるようになっています。
UPSERTの方言
各社DBとも、UPSERT文という構文は存在せず、INSERTを拡張版や MERGE文が用意されています。
MERGE文は標準SQL規格（SQL:2003）として2003年に定められており、OracleやSQL Serverなどの有償製品でサポートされています。
一方、オープンソースであるMySQL、PostgreSQLは、INSERT文を拡張した書き方で対応するなど、DBによって異なるのが現状です。
尚、PostgreSQL Ver15 からは、MERGE文が利用できるようになっています。
| DB名 | Upsert文の書き方 | 標準SQL |
|---|---|---|
| Oracle | MERGE INTO～WHEN～ | 〇 |
| MySQL | INSERT INTO ～ ON DUPLICATE ～ | ー |
| PostgreSQL | INSERT INTO ～ ON CONFLICT ～ ※Ver9.5以上でサポート MERGE INTO～WHEN～ ※Ver15以上でサポート |
△ |
| SQL Server | MERGE INTO～WHEN～ | 〇 |
| SQL Database | MERGE INTO～WHEN～ | 〇 |
| SQLite | INSERT INTO ～ ON CONFLICT ～ INSERT OR REPLACE INTO～ |
ー |
また、同じ MERGE文に対応するOracleとSQL Server においても、MERGE INTO の書き方が微妙に異なっており、DBごとの書き換えが必要となります。
UPSERTにおける挙動の違い
MERGE文は、データの存在を確認してから、それに応じた動作（INSERT、又はUPDATE）を実行します。
それに対して、INSERT INTO ～ CONFLICT（又はDUPLYCATE）は、実際にINSERTを行い、一意キーによる例外が発生した場合、UPDATEを実行します。
通常の場合はどちらでも同じ結果になりますが、プログラムAとプログラムBが同時に同じキーで更新をおなう場合、タイミングによっては MERGE文でエラーが発生します。
以下に、両者の特徴を簡単にまとめておきます。
| パターン | MEARGE文 | INSERT INTO～ CONFLICT （又はDUPLYCATE）文 |
|---|---|---|
| 既にデータが 存在する場合 |
UPDATE以外の処理 （DELETE、又は他のテーブルへのINSERT） が実行できる |
UPDATEのみ可能 （他の処理は行えない） |
| INSERTの 挙動 |
複数プログラムの同時更新の場合、タイミング によってはエラーになる可能性がある |
INSERTを実行した結果エラーが発生した時点で UPDATEに切り替えるため必ず成功する |
UPSERTを記述する上でのポイント
MARGE文の記述パターン
Oracle、SQL Server、SQL DB、PostgreSQL Ver1.5以上で利用可能なMARGE文の基本的な記述パターンは以下の通りです。
MARGEと名前が付く通り、２つのテーブルをマージすると考えた方が分かり易いでしょう。INTOで更新先、USINGで更新元のテーブルを指定し、その結合条件を ON以降に記述します。
テーブルに直接値を反映したい場合は、USING句に (SELECT 値1 AS 列名1,値2 AS 列名2,・・・）の様に記述します。この時、AS は省略しても問題ありません。
ORACLEの場合は (SELECT 値1 AS 列名1,値2 AS 列名2,・・・ FROM DUAL）という具合に、DUALテーブルからSELECTするように記述します。
上記以外にも、以下の違いがあります。
| Oracle | ①テーブル名のエイリアス定義で AS を使うと 「ORA-020212 missing USING keyword」 エラーが発生する 例 MERGE INTO TEST AS t1 ⇒ × MERGE INTO TEST t1 ⇒ 〇 ② ON～の結合条件は必ず（）で括る必要がある ②WHEN～THEN にその他の条件は指定できない |
| SQL Server | ①INSERT INTO の列名にエイリアスを付けると 「マルチパート識別子を含めることはできません」エラーが発生する 例) INSERT(t1.ID,t1.NAME) ⇒ × INSERT(ID,NAME) ⇒ 〇 ② WHEN～THEN にその他の条件が指定できる 例) WHEN MATCHED AND ID <> 200 THEN ～ |
INSERT INTO～ON CONFLICT又はON DUPLICATE文の記述パターン
MYSQL、SQLite は ON CONFLICT を使います。
一方、MYSQLは ON DUPLICATE KEY を使います。こちらの場合は、一意制約キーを指定する必要がないので、少しだけ記述が楽になります。
SQLite に関しては、INSERT OR REPLACE という便利なSQLが用意されています。しかし、UPDATE時の項目が指定できない点が異なります。
各DBごとのUPSERT例
では、それぞれについて簡単な例を紹介しておきます。
OracleのMEARGE文
MERGE INTO "商品マスタ" t1
USING (select '002' "商品ID",'イチゴミルク' "商品名",9 "数量" from dual) t2
ON (t2."商品ID"=t1."商品ID")
WHEN MATCHED THEN UPDATE SET t1."商品名" = 'トマトケチャップ'
WHEN NOT MATCHED THEN INSERT("商品ID","商品名","数量") values('002','トマトケチャップ',8)
SQL Server/SQL Database のMEARGE文
MERGE INTO "商品マスタ" t1
USING (select '002' "商品ID",'イチゴミルク' "商品名",9 "数量") t2
ON (t2."商品ID"=t1."商品ID")
WHEN MATCHED THEN UPDATE SET t1."商品名" = 'トマトケチャップ'
WHEN NOT MATCHED THEN INSERT("商品ID","商品名","数量") values('002','トマトケチャップ',8)
PostgerSQLのINSERT INTO ～ ON CONFLICT 文
MERGE INTO "商品マスタ"("商品ID","商品名","数量")
VALUES ('002','イチゴミルク',9)
ON CONFLICT ("商品ID")
DO UPDATE SET "商品名" = 'トマトケチャップ'
MySQLのINSERT INTO ～ ONDUPLICATE KEY 文
MERGE INTO "商品マスタ"("商品ID","商品名","数量")
VALUES ('002','イチゴミルク',9)
ON DUPLICATE KEY UPDATE "商品名" = 'トマトケチャップ'
SQLiteのINSERT INTO～ON CONFLICT 文
MERGE INTO "商品マスタ"("商品ID","商品名","数量")
VALUES ('002','イチゴミルク',9)
ON CONFLICT ("商品ID")
DO UPDATE SET "商品名" = 'トマトケチャップ'
まとめ
今回は、UPSERT文について Oracle,SQL Server,SQL Database,PostgreSQL,MySQL,SQLite についての記述方法について解説しました。
UPSERT文は「なければINSERT,あればUPDATE」という機能の総称であり、実際はDBごとに記述が異なります。
Oracle,SQL Server,SQL Database, PostgreSQL Ver15以降 では MERGE 文が用意されていますが、PostgreSQL Ver15未満、MySQL、SQLiteでは INSERT 文を拡張したような記述をします。
また、同じMERGE文であっても、DBによって方言があることもご紹介しました。
UPSERT文はそれほど使う機会は無いかもしれませんが、もし必要に迫られた場合は、是非この記事を思い出してください。