結構化查詢語言/MERGE

提示：小心並停用AUTOCOMMIT.

在很多情況下，應用程式希望將行儲存到資料庫中，而不必知道這些行是否之前已經存在於資料庫中。如果行存在，則必須使用 UPDATE 命令，否則使用 INSERT 命令。為此，通常使用以下結構

-- pseudocode
IF (SELECT COUNT(*) = 0 ...) THEN
  INSERT ...
ELSE
  UPDATE ...
;

這種情況在很多方面都不理想

應用程式和 DBMS 之間存在兩次往返，要麼是 SELECT + INSERT，要麼是 SELECT + UPDATE。
應用程式必須一次傳輸一行。無法進行“批次儲存”，因為決定 INSERT 和 UPDATE 之間的條件的評估可能會導致行與行之間結果不同。
語法分散在三個 SQL 語句中。這容易出錯。

為了克服這些缺點，SQL 標準定義了 MERGE 命令，該命令將上述完整程式碼包含在一個語句中。MERGE 根據目標表中單個行的存在性執行 INSERT 或 UPDATE 操作。

-- Define target, source, match criterion, INSERT and UPDATE within one single command
MERGE INTO  <target_table>      <target_table_alias>  -- denote the target table
      USING <syntaxhighlight_table>      <syntaxhighlight_table_alias>  -- denote the source table
      ON    (<match_criterion>)                       -- define the 'match criterion' which compares the source and 
                                                      -- target rows with the same syntax as in any WHERE clause  
  WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]  -- a variant of the regular UPDATE command
  WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]) -- a variant of the regular INSERT command
;

描述

目標表以 MERGE INTO 關鍵字命名，源表以 USING 關鍵字命名。

目標行和源行之間的比較（這是決定 INSERT 和 UPDATE 操作所必需的）在 ON 關鍵字之後使用與 WHERE 子句語法相同的語法指定。如果此比較匹配，則執行 UPDATE 操作，否則執行 INSERT 操作。在簡單情況下，比較將比較主鍵或外部索引鍵列。但也可以對任何列使用非常複雜的條件。

在“匹配”情況下，會執行 UPDATE 的變體。它與常規 UPDATE 命令的不同之處在於它沒有表名（表名已在 MERGE INTO 之後表示）並且沒有 WHERE 子句（它使用 ON 關鍵字之後的匹配條件）。

在“不匹配”情況下，會執行 INSERT 的變體。與之前相同的原因，目標表不會在 INSERT 中命名。

示例

建立一個表“hobby_shadow”來儲存一些“hobby”行。隨後的 MERGE 命令將根據相關行的存在性執行 INSERT 或 UPDATE 操作。

-- store every second row in a new table 'hobby_shadow'
CREATE TABLE hobby_shadow AS SELECT * FROM hobby WHERE MOD(id, 2) = 0;
SELECT * FROM hobby_shadow;

-- INSERT / UPDATE depending on the column 'id'.
MERGE INTO  hobby_shadow                   t   -- the target
      USING (SELECT id, hobbyname, remark
             FROM   hobby)                 s   -- the source
      ON    (t.id = s.id)                      -- the 'match criterion'
  WHEN MATCHED THEN
UPDATE SET remark = concat(s.remark, ' Merge / Update')
  WHEN NOT MATCHED THEN
INSERT (id, hobbyname, remark) VALUES (s.id, s.hobbyname, concat(s.remark, ' Merge / Insert'))
;
COMMIT;

-- Check the result
SELECT * FROM hobby_shadow;

MERGE 命令處理所有行，但應用程式和 DBMS 之間只有一次往返。一些行由 MERGE 的 INSERT 部分處理，另一些行由其 UPDATE 部分處理。可以透過“remark”列的最後一部分觀察這種區別。

用例

MERGE 命令的典型用例是 ETL 程序。通常，這些程序必須在一段時間內針對分組條件（例如：產品線）聚合一些值。每個產品線和期間的第一次訪問必須插入具有給定值的新行，後續訪問必須透過增加值來更新這些行。

擴充套件

SQL 標準在 MERGE 命令中定義了一些其他功能。

WHEN 子句
WHEN MATCHED 和 WHEN NOT MATCHED 子句可以使用可選的查詢表示式進行擴充套件，例如 AND (place_of_birth = 'Dallas')。因此，可以使用一系列 WHEN MATCHED/WHEN NOT MATCHED 子句。

... 
  WHEN MATCHED AND (t.hobby_name IN ('Fishing', 'Underwater Diving')) THEN
UPDATE SET remark = concat('Water sports: ', t.remark)
  WHEN MATCHED AND (t.hobby_name IN ('Astronomy', 'Microscopy', 'Literature')) THEN
UPDATE SET remark = concat('Semi-professional leisure activity: ', t.remark)
  WHEN MATCHED THEN
UPDATE SET remark = concat('Leisure activity: ', t.remark)
...
-- The same is possible with WHEN NOT MATCHED in combination with INSERT

DELETE
在 WHEN MATCHED 子句中，可以使用 DELETE 命令來代替 UPDATE 操作來刪除匹配的行。此功能可以與之前介紹的可選查詢表示式的擴充套件相結合。在 SQL 標準中，DELETE 命令不適用於 WHEN NOT MATCHED 子句。

-- Update 'Fishing' and 'Underwater Diving'. Delete all others which have a match between source and target.
...
  WHEN MATCHED AND (t.hobby_name IN ('Fishing', 'Underwater Diving')) THEN
UPDATE SET remark = concat('Water sports: ', t.remark)
  WHEN MATCHED THEN
DELETE
...

注意事項

MERGE 命令由標準 SQL 清晰定義。命令本身以及之前描述的擴充套件已由很多 DBMS 實現。不幸的是，大多數實現與標準不同，使用了不同的關鍵字和/或額外的關鍵字，有時還使用了不同的概念。即使是介紹性的關鍵字 MERGE INTO 也會與標準不同。

練習

A) 建立一個新表“contact_merge”，其結構與“contact”相同。
B) 將“contact”中的第 3 行復制到“contact_merge”。
C) 使用 MERGE 命令將“contact”中的所有電子郵件地址插入/更新到“contact_merge”，並將電子郵件協議名稱新增到聯絡值（在“contact_value”列前面新增字串 'mailto:''）。

單擊以檢視解決方案

-- Create table and copy one row
CREATE TABLE contact_merge AS SELECT * FROM contact WHERE id = 3;
SELECT * FROM contact_merge;

-- INSERT / UPDATE depending on the column 'id'.
MERGE INTO  contact_merge                             t   -- the target
      USING (SELECT id, person_id, contact_type, contact_value
             FROM   contact
             WHERE  contact_type = 'email')           s   -- the source
      ON    (t.id = s.id)                                 -- the 'match criterion'
  WHEN MATCHED THEN
UPDATE SET contact_value = concat('mailto:', t.contact_value)
  WHEN NOT MATCHED THEN
INSERT (id, person_id, contact_type, contact_value) VALUES (s.id, s.person_id, s.contact_type, concat('mailto:', s.contact_value))
;
COMMIT;
 
-- Check the result
SELECT * FROM contact_merge;

DELETE 2

結構化查詢語言
MERGE

TRUNCATE