oracle——merge用法

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;

oracle——merge用法

1

2

3

推荐阅读更多精彩内容