使用MERGE在单个语句中对表执行UPDATE和DELETE操作

来源：法临律网

下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新 AdventureWorks 示例数据库中的 ProductInventory 表。通过减去每天对 SalesOrderDetail 表中的每种产品所下的订单数，更新 ProductInventory 表的 Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少，则会从 ProductInventory 表中删除该产品对应的行。 <无> $velocityCount-->

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
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

EXECUTE Production.usp_UpdateInventory '20030501'

显示全文

全部频道

使用MERGE在单个语句中对表执行UPDATE和DELETE操作