Update calculated value using subquery

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:

itemcode	SaleQnt1999
0036876	116
5004097	NULL
5003502	120
...	...

sqlexamples.info