Peter Larsson Blog

MERGE     Production.ProductInventory AS [pi]
USING     ( 
               SELECT     ProductID, 
                          SUM(OrderQty) AS OrderQty
               FROM       Sales.SalesOrderDetail AS sod 
               INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID 
               WHERE      soh.OrderDate = GETDATE()
               GROUP BY   ProductID 
          ) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductID
WHEN      MATCHED AND src.OrderQty = 0 
               THEN DELETE; 
WHEN      MATCHED 
               THEN UPDATE SET [pi].Quantity = src.OrderQty
WHEN      NOT MATCHED 
               THEN INSERT VALUES (src.ProductID, src.OrderQty)