UPDATE FROM

What does UPDATE FROM do?

If you want to UPDATE values in tables, a script often looks like this: you assign a new value (which can be derived from other values) to a column. But how does this work if the other values come from a different table? Then you need an UPDATE FROM.

How can you work with UPDATE FROM?

Starting with a simple update, we increase the UnitPrice of all USB products in the Warehouse StockItems table by 10% in the Wideworld Importers database:

UPDATE Warehouse.StockItems
SET UnitPrice = UnitPrice*1.1 
WHERE StockItemName LIKE '%USB%'

SELECT @@ROWCOUNT

This means we have increased the price of 14 products. Now we would like to adjust the OrderLines in the Sales scheme with the new prices (although this is of course not a real-world scenario, as we would not change completed orders there). We could of course now make a construct that looks like this

UPDATE Sales.OrderLines
SET UnitPrice = UnitPrice*1.1
WHERE StockItemID IN
(
    SELECT StockItemID 
    FROM Warehouse.Stockitems 
    WHERE StockItemName LIKE '%USB%'
)

This is a bit unpleasant as we would have to apply the calculation explicitly and we have also set the filter on the USB items directly again. We can get around this by using a JOIN to find the items with changed prices:

SELECT 
     ol.[OrderLineID]
    ,ol.[OrderID]
    ,ol.[StockItemID]
    ,ol.[Description]
    ,ol.[PackageTypeID]
    ,ol.[Quantity]
    ,ol.[UnitPrice]
    ,ol.[TaxRate]
    ,ol.[PickedQuantity]
    ,ol.[PickingCompletedWhen]
    ,ol.[LastEditedBy]
    ,ol.[LastEditedWhen] 
FROM Sales.OrderLines ol  
INNER JOIN Warehouse.StockItems si 
ON ol.StockItemID = si.StockItemID
AND si.UnitPrice <> ol.UnitPrice

We can now use this JOIN to change the prices on the 14,834 OrderLines where the prices no longer match the updated StockItem table:

UPDATE ol
SET UnitPrice = si.UnitPrice
FROM Sales.OrderLines ol  
INNER JOIN Warehouse.StockItems si 
ON ol.StockItemID = si.StockItemID
AND si.UnitPrice <> ol.UnitPrice

SELECT @@ROWCOUNT

Together with CTEs and all possible JOIN and WHERE conditions, this results in the most interesting UPDATE constructs. However, it is recommended to do a round in the Dojo and to check by SELECT if only the lines that should be updated are really updated (as generally with every UPDATE)…

References:

• Official documentation from Microsoft