ادغام (SQL)

یک سیستم مدیریت پایگاه داده رابطه‌ای از عبارات SQL MERGE (همچنین upsert نام دارد) برای INSERT رکوردهای جدید یا UPDATE رکوردهای موجود بسته به اینکه شرط مطابقت دارد، استفاده می‌کند. به طور رسمی در استاندارد SQL:2003 معرفی شد و در استاندارد SQL:2008 گسترش یافت .

استفاده

MERGE INTO tablename USING table_reference ON (condition)
  WHEN MATCHED THEN
    UPDATE SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

یک پیوست راست(right join) روی Target(هدف) (جدول INTO) و Source(منبع) (جدول / view / کوئری فرعی در حال استفاده) استفاده می شود - که در آن Target جدول سمت چپ و Source جدول سمت راست است. چهار ترکیب ممکن این قوانین را دارند:

• اگر فیلد(های) ON در Source با فیلد(های) ON در Target مطابقت داشته باشد، آنگاه UPDATE انجام می شود.
• اگر فیلد(های) ON در منبع با فیلد(های) ON در Target مطابقت نداشته باشد،آنگاه INSERT انجام می شود.
• اگر فیلد(های) ON در Source وجود نداشته باشد اما در Target وجود داشته باشد، هیچ کاری انجام نمی شود.
• اگر فیلد(های) ON در Source یا Target وجود نداشته باشد، هیچ کاری انجام نمی شود.

اگر چندین ردیف منبع با یک ردیف هدف مطابقت داشته باشند، یک خطا توسط استانداردهای SQL:2003 به وجود می اید. شما نمی توانید یک ردیف هدف را چندین بار با یک عبارت MERGE اپدیت کنید.

پیاده سازی ها

سیستم های مدیریت پایگاه داده PostgreSQL ، Oracle Database ، IBM Db2 ، Teradata ، EXASOL ، Firebird ، CUBRID ، H2 ، HSQLDB ، MS SQL ، Vectorwise و Apache Derby از گرامر(سینتکس) استاندارد پشتیبانی می کنند. برخی نیز افزونه های غیر استاندارد SQL را اضافه می کنند.

مترادف

برخی از پیاده‌سازی‌های پایگاه داده، اصطلاح Upsert ( نوعی به‌روزرسانی و درج ) را برای دستور پایگاه داده یا ترکیبی از عبارات به کار می‌گیرند که در صورت عدم وجود رکورد یک رکورد را به جدولی در پایگاه داده وارد می‌کند. یا اگر رکورد قبلاً وجود داشته باشد، ، رکورد موجود را به روز می کند. این مترادف در PostgreSQL (v9.5+) و SQLite (v3.24+) استفاده می شود. همچنین برای مخفف شبه کد معادل "MERGE" استفاده می شود.

در پایگاه داده Microsoft Azure SQL استفاده می شود.

سایر پیاده سازی های غیر استاندارد

برخی دیگر از سیستم های مدیریت پایگاه داده از طریق افزونه های غیر استاندارد SQL خود از این رفتار یا رفتار بسیار مشابه پشتیبانی می کنند.

برای مثال MySQL از INSERT ... ON DUPLICATE KEY UPDATEپشتیبانی می کند که می تواند برای دستیابی به یک نتیجه یکسان با این محدودیت استفاده شود که اتصال بین هدف و منبع باید فقط بر اساس PRIMARY KEY یا UNIQUE انجام شود، که در استاندارد ANSI/ISO مورد نیاز نیست. همچنین از دستور >REPLACE INTO پشتیبانی می‌کند، که ابتدا یک درج را انجام می‌دهد، و اگر موفق نشد، ردیف را در صورت وجود حذف می‌کند، و سپس ردیف جدید را درج می‌کند. همچنین یک بند IGNORE برای عبارت INSERT وجود دارد، که به سرور می‌گوید خطاهای «کلید تکراری» را نادیده بگیرد و ادامه دهد (ردیف‌های موجود درج یا به‌روزرسانی نمی‌شوند، اما تمام ردیف‌های جدید درج خواهند شد).

INSERT OR REPLACE INTO اس کیو ال لایت به طور مشابه کار می کند. همچنین از REPLACE INTO به عنوان نام مستعار برای سازگاری با MySQL پشتیبانی می کند.

Firebird از MERGE INTO پشتیبانی می‌کند، اما در صورت وجود چندین ردیف داده منبع، خطا ایجاد نمی‌کند. علاوه بر این یک نسخه تک خطی، UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)] وجود دارد ، اما دومی به شما این امکان را نمی‌دهد که اقدامات مختلفی را برای درج در مقابل به‌روزرسانی انجام دهید (مثلاً تنظیم یک مقدار دنباله جدید فقط برای ردیف‌های جدید، نه برای ردیف‌های موجود).

IBM Db2 گرامر را با چند عبارت WHEN MATCHED و WHEN NOT MATCHED گسترش می دهد و آنها را با ... AND some-condition و ... AND some-condition تشخیص میدهد

Microsoft SQL Server با محافظ های پشتیبانی و همچنین با پشتیبانی از Left Join از طریق WHEN NOT MATCHED BY SOURCE گسترش می یابد.

PostgreSQL از ادغام از نسخه 15 پشتیبانی می کند اما قبلاً از ادغام از طریق INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_actionپشتیبانی می کرد .

CUBRID از عبارت MERGE INTO پشتیبانی می کند. و استفاده از INSERT ... ON DUPLICATE KEY UPDATEپشتیبانی می کند. همچنین از REPLACE INTO برای سازگاری با MySQL پشتیبانی می کند.

Apache Phoenix از دستور های UPSERT VALUES و UPSERT SELECT پشتیبانی می کند.

Spark SQL از بندهای UPDATE SET * و INSERT *پشتیبانی می کند.

Apache Impalaاز UPSERT INTO ... SELECTپشتیبانی می کند .

استفاده از NoSQL

مفهوم مشابهی در برخی از پایگاه های داده NoSQL وجود دارد.

به عنوان مثال در MongoDB فیلدهای با یک مقدار مرتبط با یک کلید را می توان با یک عملیات update به روز کرد. اگر کلید پیدا نشود، update با خطا مواجه می‌شود. در عملیات update امکان تنظیم پرچم upsert وجود دارد: در این حالت یک مقدار جدید مرتبط با کلید داده شده در صورتی که وجود نداشته باشد ذخیره می شود، در غیر این صورت کل مقدار جایگزین می شود.

در Redis عملیات SET مقدار مربوط به یک کلید داده شده را تعیین می کند. Redis هیچ جزئیاتی از ساختار داخلی داده را نمی‌داند، بنابراین به‌روزرسانی معنایی نخواهد داشت. بنابراین عملیات SET همیشه یک مجموعه یا جایگزین معنایی دارد.

منابع

1. "E.1. Release 15". PostgreSQL Documentation (به انگلیسی). 13 October 2022. Archived from the original on 13 October 2022. Retrieved 13 October 2022.
2. "PostgreSQL Upsert Using INSERT ON CONFLICT statement". PostgreSQL Tutorial. Archived from the original on Nov 28, 2022.
3. "upsert", SQLite, visited 6-6-2018.
4. "MERGE (Transact-SQL)". Transact-SQL Reference (Database Engine). Microsoft Learn. Archived from the original on Jun 24, 2016.
5. MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
6. MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
7. "MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax". Retrieved 29 October 2013.
8. "SQL As Understood By SQLite: INSERT". Retrieved 2012-09-27.
9. PostgreSQL INSERT page
10. "New CUBRID 9.0.0". CUBRID Official Blog. 2012-10-30. Retrieved 2012-11-08.
11. CUBRID :: Data Manipulation Statements :: Insert :: ON DUPLICATE KEY UPDATE Clause
12. CUBRID :: Data Manipulation Statements :: Replace
13. "UPSERT VALUES".
14. "UPSERT SELECT".
15. "MERGE INTO (Delta Lake on Databricks)".
16. "UPSERT Statement (Apache Impala Documentation)".

لینک های خارجی

• Oracle 11g Release 2 documentation on MERGE
• Firebird 2.1 documentation on MERGE
• DB2 v9 MERGE statement
• Microsoft SQL Server documentation
• HSQLdb 2.0 Data Change Statements
• H2 (1.2) SQL Syntax page