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

SQL入門
この記事は約7分で読めます。

この記事は、Upsertについての概要と、主なDBで具体的にどう記述するかをザックリ知りたい方向けの記事になっています。

主なDBとして Oracle,SQL Server,SQL Database,PostgreSQL,MySQL,SQLiteについて取り扱っており、それぞれ具体的なSQLを記載しているので、辞書的な使い方もできるかと思います。

UPSERTとは

「INSERT文を実行する際、すでにデータが存在すれば UPDATEを実行し、なければそのままINSERT文を実行する」という動作を意味します。

通常はテーブル間で同期をとりたい場合に用いますが、複数人数でデータを同時更新するような場合にも利用されます。

これから更新しようとするデータが、対象テーブルに存在するか分かっていれば、UPSERTは必要ありません。しかし、プログラムやシステムの要件によっては分からないケースもあります。

以前は、事前にSELECTして存在を確認してからINSERTするか、あるいは必ずDELETEしてからINSERTするという方法が多く用いられていました。

これでも要件は満たせはしますが、SQL文を2回発行する必要があるため処理速度が遅くなりますし、対象テーブルに更新日時があった場合、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
OracleMERGE INTO~WHEN~
MySQLINSERT INTO ~ ON DUPLICATE ~
PostgreSQLINSERT INTO ~ ON CONFLICT ~ ※Ver9.5以上でサポート
MERGE INTO~WHEN~ ※Ver15以上でサポート
SQL ServerMERGE INTO~WHEN~
SQL DatabaseMERGE INTO~WHEN~
SQLiteINSERT 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~ CONFLICTINSERT 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文の基本的な記述パターンは以下の通りです。

MERGE文のSQL文の画像

MARGEと名前が付く通り、2つのテーブルをマージすると考えた方が分かり易いでしょう。INTOで更新先、USINGで更新元のテーブルを指定し、その結合条件を ON以降に記述します。

テーブルに直接値を反映したい場合は、USING句に (SELECT 値1 AS 列名1,値2 AS 列名2,・・・)の様に記述します。この時、AS は省略しても問題ありません。

MERGE文のSQL文の画像(SQL Server)

ORACLEの場合は (SELECT 値1 AS 列名1,値2 AS 列名2,・・・ FROM DUAL)という具合に、DUALテーブルからSELECTするように記述します。

MERGE文のSQL文の画像(ORACLE)

上記以外にも、以下の違いがあります。

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の後には列名をカンマ区切りで列挙します。

INSERT INTO CONFLICTのが画像

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

INSERT INTO DUPLICATE KEY のが画像

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

INSERT OR REPLACE

各DBごとのUPSERT例

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

OracleのMEARGE文

SQL Server/SQL Database のMEARGE文

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

PostgerSQLのINSERT INTO ~ ON CONFLICT 文

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

より詳しい情報は「【実用】PostgreSQLで使うUpsertの書き方と注意点」に記載していますので、興味のある方はご一読ください。

MySQLのINSERT INTO ~ ON DUPLICATE KEY 文

SQLiteのINSERT INTO~ON CONFLICT 文

まとめ

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

タイトルとURLをコピーしました