SQLのmergeについてまとめています。
目次
SQLのmergeの構文
mergeは、レコードのインサート時に指定した条件次第で更新または挿入を1文で実行可能なSQLです。標準SQLではないため、各DBMSによって実装が異なります。DBMSによっては、merge自体が存在せず、別の代替機能が用意されているケースもあります。
Oracleのmergeの構文
Oracleのmergeは、update/insert以外にdeleteも実行可能。マッチング条件を指定することで2テーブルの複雑な連携が可能です。
以下は、テーブルbounsesに条件マッチした場合（WHEN MATCHED)はupdateを、条件にマッチしない場合（WHEN NOT MATCHED ）にはinsertを行う例です。
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);
SELECT * FROM bonuses ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100
MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);
SELECT * FROM bonuses ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
159 180
160 175
161 170
164 72
165 68
166 64
167 62
171 74
172 73
173 61
179 62
SQL Serverのmergeの構文
以下は、SQL Serverのストアドプロシジャに、mergeを組み込んだ例です。在庫数（Quantity）より注文数（OrderQty）が大きくなり在庫0になった場合にはDELETE、そうでなければ在庫から注文数を指し引いた値を在庫に数としてUPDATEします。
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity,
Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO
EXECUTE Production.usp_UpdateInventory '20030501'
参考）MERGE (Transact-SQL) – SQL Server
MySQLのmergeの構文
MySQLには、merge文は存在しません。mergeと同様の動作は、replaceまたはinsert on duplicate key updateで記述することが可能です。
mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.04 sec) mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42'); Query OK, 2 rows affected (0.04 sec) mysql> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | New | 2014-08-20 18:47:42 | +----+------+---------------------+ 1 row in set (0.00 sec)
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.8 REPLACE 構文
insert on duplicate key updateは、キーが重複していた場合はupdateを実行します。
INSERT `employees` set `emp_no` = 2, `birth_date` = '1983-08-08', `first_name` = 'Hanako', `last_name` = 'Tanaka', `gender` = 'F', `hire_date` = '2002-04-01' on duplicate key UPDATE `emp_no` = 2, `birth_date` = '1983-08-08', `first_name` = 'Hanako2', `last_name` = 'Tanaka', `gender` = 'F', `hire_date` = '2002-04-01'
【関連記事】
▶MySQL Insert サンプルコード集 一文で一括挿入する方法とは？
PostgreSQLのmergeの構文
PostgreSQLには、merge文は存在しません。mergeと同様の動作は、insert文にon conflictとdo updateを指定することで記述可能です。
INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
関連）PostgreSQLの公式リファレンスマニュアル：INSERT
まとめ
- mergeは対象テーブルにデータが存在しなければinsert、存在すればupdateを実行可能。
- マッチング条件次第で、2つのテーブルを複雑に連携可能。
- OracleとSQL Serverでは、mergeが使用可能。MySQLやPostgreSQLでは、代替方法で実現可能
