TSQL -> Update calculated value using subquery

The mission is to calculate quantity of items sold in 1999 and add the resulting number into Items table. Here we use single update statement that uses join between Items table and subquery that calculates quantities. This way looks like a shortest one compared to other scenarios. Although in some cases you will definitely prefer the use of TEMP table or Indexed View because of performance considerations.

-- create new column in Items table
ALTER TABLE dbo.items ADD SaleQnt1999 INT;

-- update column SaleQnt1999 using quantity calculated in subquery
UPDATE itm
SET itm.SaleQnt1999 = sb.totqnt
FROM dbo.items AS itm INNER JOIN
  (SELECT a.itemcode,   SUM(isNull(a.qnt,0)) AS totqnt
  FROM dbo.InvLines AS a INNER JOIN dbo.Invoice AS b
  ON a.dockey = b.dockey
  WHERE Year(b.docdate)=1999
  GROUP BY a.itemcode) AS sb
ON itm.itemcode = sb.itemcode;

SELECT itemcode, SaleQnt1999 FROM dbo.items ORDER BY itemcode;


The result will be like this:
itemcodeSaleQnt1999
0036876116
5004097NULL
5003502120
......




sqlexamples.info