1
假設(shè)有兩個(gè)表簸搞，Employees（員工表）和NewHires（新入職員工表）。你想將NewHires表中的新員工信息合并到Employees表中，如果新員工已存在則更新其信息，如果不存在則插入新員工信息旅赢，并刪除Employees表中已離職的員工信息（假設(shè)離職狀態(tài)在Employees表中有標(biāo)記）。
MERGE INTO Employees AS E
USING NewHires AS N
ON E.EmployeeID = N.EmployeeID
WHEN MATCHED THEN
UPDATE SET E.Name = N.Name, E.StartDate = N.StartDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (EmployeeID, Name, StartDate)
VALUES (N.EmployeeID, N.Name, N.StartDate)
WHEN MATCHED AND E.IsTerminated = 1 THEN
DELETE;
2
image.png
merge into student s
using boy b on (s.id=b.id)
when matched then
update set s.name=b.name
when not matched then
insert values(b.id,b.name,'男')惑惶；
3
在ON子句中煮盼，可以使用多個(gè)條件來(lái)更精確地控制哪些記錄應(yīng)該被合并。
假設(shè)你有兩個(gè)表带污，Orders（訂單表）和OrderDetails（訂單詳情表）僵控，你想根據(jù)訂單ID和客戶(hù)ID來(lái)更新訂單的總金額。
MERGE INTO Orders AS O
USING (
SELECT OrderID, CustomerID, SUM(Quantity * Price) AS TotalAmount
FROM OrderDetails
GROUP BY OrderID, CustomerID
) AS OD
ON O.OrderID = OD.OrderID AND O.CustomerID = OD.CustomerID
WHEN MATCHED THEN
UPDATE SET O.TotalAmount = OD.TotalAmount;
