【総まとめ】UPSERTとは？DBごとの違いは？

この記事は、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	INSERT INTO～ DUPLYCATE
既にデータが存在する場合	UPDATE以外の処理 （DELETE、又は他のテーブルへのINSERT）が実行できる	UPDATEのみ可能 （他の処理は行えない）	UPDATEのみ可能 （他の処理は行えない）
INSERTの挙動	複数プログラムの同時更新の場合、タイミングによってはエラーになる可能性がある	INSERTを実行した結果エラーが発生した時点でUPDATEに切り替えるた め、必ず成功する	INSERTを実行した結果エラーが発生した時点でUPDATEに切り替えるため、必ず成功する
存在すれば何もしない場合	WHEN MATCHED THEN 句を書かない	DO UPDATE SET ～の代わりにDO NOTING を記述する	INSERT IGNORE INTO～ を使用する

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文の記述パターン

PostgreSQL、SQLite は ON CONFLICT を使います。この時、CONFLICTの後には列名をカンマ区切りで列挙します。

一方、MYSQLは ON DUPLICATE KEY を使います。こちらの場合は、一意制約キーを指定する必要がないので、少しだけ記述が楽になります。

SQLite に関しては、INSERT OR REPLACE という便利なSQLが用意されています。しかし、UPDATE時の項目が指定できない点が異なります。

各DBごとのUPSERT例

では、それぞれについて簡単な例を紹介しておきます。

OracleのMEARGE文

MERGE INTO "商品マスタ"  t1
USING (select '002' as "商品ID",'イチゴミルク' as "商品名",15 as "数量" from dual)  t2
ON  (t2."商品ID"=t1."商品ID")
WHEN MATCHED THEN UPDATE SET  t1."商品名" = 'イチゴミルク'
WHEN NOT MATCHED THEN  INSERT("商品ID","商品名","数量") values('002','イチゴミルク',15)

SQL Server/SQL Database のMEARGE文

MERGE INTO "商品マスタ"  t1
USING (select '002' as "商品ID",'イチゴミルク' as "商品名",15 as "数量")  t2
ON  (t2."商品ID"=t1."商品ID")
WHEN MATCHED THEN UPDATE SET  t1."数量" = 15
WHEN NOT MATCHED THEN  INSERT("商品ID","商品名","数量") values('002','イチゴミルク',15);

※文の末尾にセミコロン ’；’ を指定しないとエラーになるので注意。

より詳しい情報は「【実用】SQL ServerにおけるUpsertの書き方（サンプル付き）」に記載していますので、必要に応じてご一読下さい。

PostgerSQLのINSERT INTO ～ ON CONFLICT 文

INSERT INTO "商品マスタ"("商品ID","商品名","数量")
VALUES ('002','イチゴミルク',15)
ON  CONFLICT ("商品ID")
DO UPDATE SET "数量" = 15

※テーブルには、プライマリキーとは別にUNIQUE制約を付与しておく必要があります。

より詳しい情報は「【実用】PostgreSQLで使うUpsertの書き方と注意点」に記載していますので、必要に応じてご一読下さい。

MySQLのINSERT INTO ～ ON DUPLICATE KEY 文

INSERT INTO "商品マスタ"("商品ID","商品名","数量")
VALUES ('002','イチゴミルク',15)
ON DUPLICATE KEY UPDATE "数量名" = 15

より詳しい情報は「【詳しく解説】MySQLにおけるUpsertの書き方（サンプル付き）」に記載していますので、必要に応じてご一読下さい。

SQLiteのINSERT INTO～ON CONFLICT 文

INSERT INTO "商品マスタ"("商品ID","商品名","数量")
VALUES ('002','イチゴミルク',15)
ON  CONFLICT ("商品ID")
DO UPDATE SET "数量" = 15

より詳しい情報は「【詳しく解説】SQLiteにおけるUpsertの書き方（サンプル付き）」に記載していますので、必要に応じてご一読下さい。

まとめ

今回は、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文はそれほど使う機会は無いかもしれませんが、もし必要に迫られた場合は、是非この記事を思い出してください。