Use MERGE to perform UPDATE and DELETE operations on a table in a single statement

The following example uses MERGE to update the ProductInventory table in the AdventureWorks sample database every day based on the processed orders in the SalesOrderDetail table. Update the Quantity column of the ProductInventory table by subtracting the number of orders for each product in the SalesOrderDetail table every day. If the subscription of a product

The following example uses MERGE to update the ProductInventory table in the AdventureWorks sample database every day based on the processed orders in the SalesOrderDetail table. Update the Quantity column of the ProductInventory table by subtracting the number of orders for each product in the SalesOrderDetail table every day. If the subscription of a product

The following example uses MERGE to update the ProductInventory table in the AdventureWorks sample database every day based on the processed orders in the SalesOrderDetail table. Update the Quantity column of the ProductInventory table by subtracting the number of orders for each product in the SalesOrderDetail table every day. If the order number of a product causes the inventory of the product to drop to 0 or less, the corresponding row of the product will be deleted from the ProductInventory table. <无>

USE AdventureWorks2012;GOIF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;GOCREATE PROCEDURE Production.usp_UpdateInventory    @OrderDate datetimeASMERGE Production.ProductInventory AS targetUSING (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 DELETEWHEN 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;GOEXECUTE Production.usp_UpdateInventory '20030501'