マージ (SQL)

リレーショナルデータベース管理システムは、条件が一致するかどうかに応じて、新しいレコードまたは既存のレコードに対してSQL MERGE ( upsertとも呼ばれる) ステートメントを使用します。これはSQL:2003標準で正式に導入され、SQL:2008標準で拡張されました^[^{引用が必要}^]。 INSERTUPDATEDELETE

使用法

MERGE INTO tablename USING table_reference ON ( condition )一致した場合はUPDATE SET column1 = value1 [ 、column2 = value2 ... ]一致しなかった場合はINSERT ( column1 [ 、column2 ... ] ) VALUES ( value1 [ 、value2 ... ] );

右結合は、ターゲット (INTO テーブル) とソース (USING テーブル/ビュー/サブクエリ) に対して使用されます。ここで、ターゲットは左側のテーブル、ソースは右側のテーブルです。4 つの可能な組み合わせにより、次のルールが生成されます。

ソースのONフィールドがターゲットのONフィールドと一致する場合は、UPDATE
ソースのONフィールドがターゲットのONフィールドと一致しない場合は、INSERT
ON フィールドがソースに存在しないがターゲットに存在する場合、アクションは実行されません。
ON フィールドがソースまたはターゲットのどちらにも存在しない場合、アクションは実行されません。

複数のソース行が特定のターゲット行と一致する場合、SQL:2003 標準ではエラーが必須です。MERGE ステートメントを使用してターゲット行を複数回更新することはできません。

実装

データベース管理システムPostgreSQL、^[1]、 Oracle Database、IBM Db2、Teradata、EXASOL、Firebird、CUBRID、H2、HSQLDB、MS SQL、Vectorwise、Apache Derbyは標準構文をサポートしています。非標準のSQL拡張機能を追加するものもあります。

同義語

一部のデータベース実装では、データベースのステートメントまたはステートメントの組み合わせにupsert ( updateとinsertを組み合わせた造語)という用語が採用されています。これは、レコードが存在しない場合にデータベースのテーブルにレコードを挿入し、レコードが既に存在する場合は既存のレコードを更新します。この同義語は、PostgreSQL (v9.5+) ^[2]およびSQLite (v3.24+) ^[3]で使用されています。また、同等の "MERGE" 疑似コードを省略するためにも使用されます。

Microsoft Azure SQL Databaseで使用されています。^[4]

その他の非標準実装

他のデータベース管理システムの中には、独自の非標準 SQL 拡張機能を通じて、これまたは非常に類似した動作をサポートするものもあります。

たとえば、MySQL は、ターゲットとソースの結合が PRIMARY KEY または UNIQUE 制約でのみ実行されなければならないという制限付きで、同様の効果を達成できる構文 [5] の使用をサポートしています。これは、ANSI/ISO 標準では必須ではありません。また、最初^に挿入を試み、それが失敗した場合は行を削除し (存在する場合)、新しい行を挿入する構文 [6] もサポートしています。また、ステートメントには、サーバーに「重複キー」エラーを無視して続行するように指示する句 [7] もあります(^既存の行^は挿入または更新されませんが、すべての新しい行が挿入されます)。 INSERT ... ON DUPLICATE KEY UPDATE>REPLACE INTOIGNOREINSERT

SQLiteも同様に動作します。MySQLとの互換性のために別名としてもサポートされています。^[8]INSERT OR REPLACE INTOREPLACE INTO

Firebird は、MERGE INTO複数のソースデータ行がある場合にエラーをスローすることをサポートしていますが、失敗します。さらに、単一行バージョンもありますが、後者では、挿入と更新で異なるアクションを実行するオプションは提供されません (たとえば、新しい行にのみ新しいシーケンス値を設定し、既存の行には設定しないなど)。 UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)]

IBM Db2 はWHEN MATCHED、複数のand句を使用して構文を拡張し、それらをガードWHEN NOT MATCHEDで区別します。 ... AND some-condition

Microsoft SQL Server は、ガードのサポートと、句を介した Left Join のサポートによって拡張されています。 WHEN NOT MATCHED BY SOURCE

PostgreSQLはバージョン15以降でマージをサポートしていますが、以前は経由でマージをサポートしていました。^[9]INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action

CUBRIDはMERGE INTO^[10]ステートメントをサポートしています。また、構文の使用をサポートしています。 ^[11]また、MySQLとの互換性もサポートしています。^[12]INSERT ... ON DUPLICATE KEY UPDATEREPLACE INTO

Apache PhoenixはUPSERT VALUES^[13]とUPSERT SELECT^[14]の構文をサポートしています。

Spark SQLはUPDATE SET *アクション内でand句をサポートしていますINSERT *。^[15]

Apache ImpalaはサポートしていますUPSERT INTO ... SELECT。^[16]

NoSQLでの使用

同様の概念が一部のNoSQLデータベースにも適用されています。

MongoDBでは、キーに関連付けられた値のフィールドをupdate操作で更新できます。updateキーが見つからない場合はエラーが発生します。update操作ではフラグを設定できますupsert。この場合、指定されたキーが存在しない場合は、そのキーに関連付けられた新しい値が格納され、存在する場合は値全体が置き換えられます。

Redisでは、操作SETによって、指定されたキーに関連付けられた値が設定されます。Redis は値の内部構造の詳細を認識しないため、更新は意味を持ちません。したがって、操作には常に設定または置換のSETセマンティクスが存在します。

参照

特に参加:
- 結合 (SQL)
- 参加 (Unix)

参考文献

^ “E.1. リリース 15”. PostgreSQL ドキュメント. 2022年10月13日. 2022年10月13日時点のオリジナルよりアーカイブ。 2022年10月13日閲覧。
^ 「INSERT ON CONFLICT ステートメントを使用した PostgreSQL Upsert」。PostgreSQLチュートリアル。2022 年 11 月 28 日時点のオリジナルよりアーカイブ。
^ 「upsert」、SQLite、2018 年 6 月 6 日にアクセス。
^ 「MERGE (Transact-SQL)」。Transact-SQL リファレンス (データベースエンジン)。Microsoft Learn。2016 年 6 月 24 日時点のオリジナルよりアーカイブ。
^ MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE 構文
^ MySQL 5.1 リファレンスマニュアル: 11.2.6 REPLACE 構文
^ 「MySQL 5.5 リファレンスマニュアル :: 13.2.5 INSERT 構文」。2013 年10 月 29 日閲覧。
^ 「SQLite で理解する SQL: INSERT」。2012 年 9 月 27 日閲覧。
^ PostgreSQL INSERT ページ
^ 「新しいCUBRID 9.0.0」。CUBRID公式ブログ。2012年10月30日。 2012年11月8日閲覧。
^ CUBRID :: データ操作文 :: 挿入 :: ON DUPLICATE KEY UPDATE 句
^ CUBRID :: データ操作ステートメント :: 置換
^ 「UPSERT VALUES」。
^ 「UPSERT SELECT」。
^ 「MERGE INTO (Databricks 上の Delta Lake)」。
^ 「UPSERT ステートメント (Apache Impala ドキュメント)」。

Hsu, Leo; Obe, Regina (2008 年 5 月 18 日)。「SQL Server、MySQL、PostgreSQL の相互比較」。Postgres OnLine Journal。2010年10 月 8 日閲覧。
Chodorow , Kristina、Mike Dirolf (2010 年 9 月)。MongoDB : The Definitive Guide。O'Reilly。ISBN 978-1-449-38156-1。

外部リンク

Oracle 11gリリース2のドキュメントMERGE
Firebird 2.1のドキュメントMERGE
DB2 v9 MERGE ステートメント
Microsoft SQL Server ドキュメント
HSQLdb 2.0 データ変更ステートメント
H2 (1.2) SQL構文ページ

[1] “E.1. リリース 15”. PostgreSQL ドキュメント. 2022年10月13日. 2022年10月13日時点のオリジナルよりアーカイブ。 2022年10月13日閲覧。

[2] 「INSERT ON CONFLICT ステートメントを使用した PostgreSQL Upsert」。PostgreSQLチュートリアル。2022 年 11 月 28 日時点のオリジナルよりアーカイブ。

[3] 「upsert」、SQLite、2018 年 6 月 6 日にアクセス。

[4] 「MERGE (Transact-SQL)」。Transact-SQL リファレンス (データベースエンジン)。Microsoft Learn。2016 年 6 月 24 日時点のオリジナルよりアーカイブ。

[5] MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

[6] MySQL 5.1 リファレンスマニュアル: 11.2.6 REPLACE 構文

[7] 「MySQL 5.5 リファレンスマニュアル :: 13.2.5 INSERT 構文」。2013 年10 月 29 日閲覧。

[8] 「SQLite で理解する SQL: INSERT」。2012 年 9 月 27 日閲覧。

[9] PostgreSQL INSERT ページ

[10] 「新しいCUBRID 9.0.0」。CUBRID公式ブログ。2012年10月30日。 2012年11月8日閲覧。

[11] CUBRID :: データ操作文 :: 挿入 :: ON DUPLICATE KEY UPDATE 句

[12] CUBRID :: データ操作ステートメント :: 置換

[13] 「UPSERT VALUES」。

[14] 「UPSERT SELECT」。

[15] 「MERGE INTO (Databricks 上の Delta Lake)」。

[16] 「UPSERT ステートメント (Apache Impala ドキュメント)」。