Search code examples
sqlsql-serverselectsql-update

How to UPDATE everything from SELECT in SQL Server

I have a table called InventoryTransfer which contains the summary information of the inventory transfer. It looks like this:

enter image description here

I also have another table called InventoryTransferDetails which contains the items that were transferred as shown here:

enter image description here

I have another table that contains the current quantity of all the items which is named Inventory.

enter image description here

Now my application makes use of the CTransferID value to add the items into the Inventory table. Currently, my application first retrieves the items under InventoryTransferDetails and loops to add them to the Inventory table at application level. While it works, I know it's not very efficient.

So for CTransferID value 1009, we have 2 items namely ORQ and MKX. What I would like to do is Update the values ORQ and MKX inside Inventory table with the quantity specified in InventoryTransferDetails. In this case, the final values of ORQ and MKX inside Inventory table would now be +18 and +8 respectively.

My logic would be similar to the statement below. This statement is just an example to demonstrate my logic.

UPDATE GBInventory i
SET i.CQuantity += td.CQuantity
FROM (
SELECT * 
FROM GBInventoryTransferDetails) AS td
WHERE td.CTransferID = 1008 
AND i.CSite = 'Store'
AND i.CBarcode = td.CBarcode;

Thank you so much.

Solution

  • Seems you just need to remove the SELECT * line, and move the first Inventory T1 table reference into the FROM using a JOIN

    Better table aliases would be useful also.

    UPDATE i
SET CQuantity += td.CQuantity
FROM Inventory i
JOIN InventoryTransferDetails td
   ON i.CBarcode = td.CBarcode
WHERE td.CTransferID = 1009 
  AND i.CSite = 'Store';

    If you want to see the data as well, you can also add an OUTPUT clause.

    You can also do this using a SELECT inside a CTE, which can often make it easier for debugging. Note that the CTE alias is updated directly, do not join or name the tables again.

    WITH ToUpdate AS (
    SELECT
      i.*,
      NewQuantity = i.CQuantity + td.CQuantity
    FROM Inventory i
    JOIN InventoryTransferDetails td
       ON i.CBarcode = td.CBarcode
    WHERE td.CTransferID = 1009 
      AND i.CSite = 'Store'
)
UPDATE ToUpdate
SET CQuantity = NewCQuantity;