1
假设有两个表，Employees（员工表）和NewHires（新入职员工表）。你想将NewHires表中的新员工信息合并到Employees表中，如果新员工已存在则更新其信息，如果不存在则插入新员工信息，并删除Employees表中已离职的员工信息（假设离职状态在Employees表中有标记）。
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子句中，可以使用多个条件来更精确地控制哪些记录应该被合并。
假设你有两个表，Orders（订单表）和OrderDetails（订单详情表），你想根据订单ID和客户ID来更新订单的总金额。
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;
