使用merge在单个语句中执行insert和update操作

SQL Server (2.1万) 2014-09-10 11:13:01

    在AdventureWorks数据库中有ProductInventory表,存储的是存货信息,SalesOrderDetail表中存储的是订单信息,现在如果每天减去对SalesOrderDetail表中每种产品所下的订单数,更新ProductInventory表中的 Quantity列。如果随着时间推移订单数导致产品库存量下降到0或者更少,则从ProductInventory表中删除该产品对应的行。下面的语句创建一个存储过程实现上面的逻辑。

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

    这个语句比第一个要复杂一点,注意当匹配成功并且总量小于0的时候直接使用一个delete就可以将此条记录删除,output语句直接把操作结果输出,相当地神奇。最后运行下面的 语句得到如图3的结果。注意这个语句相当于将2003年5月1号的订单量减去。如果多次运行的话就相当于多减了一次,整个表中数据条数会减少的。

    EXECUTE Production.usp_UpdateInventory '20030501'

    使用merge在单个语句中执行insert和update操作 (https://www.tiejiang.org/) SQL Server 第1张

作者:Tyler Ning 
出处:http://www.cnblogs.com/tylerdonet/ 

THE END

发表评论