1

假設(shè)有兩個表，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子句中，可以使用多個條件來更精確地控制哪些記錄應(yīng)該被合并。
假設(shè)你有兩個表，Orders（訂單表）和OrderDetails（訂單詳情表），你想根據(jù)訂單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;