In the “Working with 1 or all items” blog in this section, I showed how to select 1 or all items. I start with a table of item number(s) to update. This table contains the item number and the location code. I create a cursor to iterate over the result set, loading the item number and location code into my local variables, @itemNumb, and @locncode.
Since I need to sum the quantities of an item at a location, I need to create a derived table to contain the sum. I do this with a JOIN (on the SOP10200 table) with the IV00102 table, joining on ITEMNMBR and LOCNCODE. To make a sanity check, I do a SELECT rather than an UPDATE. That way, I can verify that my query is correct. Once I am satisfied, I comment out the SELECT and implement the UPDATE.
OPEN SOPCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM SOPCursor INTO @itemNumb, @locncode
if @@fetch_status <> 0 begin
break
end
UPDATE dbo.IV00102 SET ATYALLOC = N.QTY
-- SELECT I.ATYALLOC, N.*
FROM dbo.IV00102 I
JOIN (
SELECT L.ITEMNMBR, L.LOCNCODE, SUM(L.QUANTITY) AS QTY
FROM dbo.SOP10200 L
WHERE L.ITEMNMBR = @itemNumb AND L.LOCNCODE = @locncode AND L.SOPTYPE = '2'
GROUP BY L.ITEMNMBR, L.LOCNCODE
) N ON I.ITEMNMBR = N.ITEMNMBR AND I.LOCNCODE = N.LOCNCODE
END
CLOSE SOPCursor
DEALLOCATE SOPCursor