October 11, 2006 at 11:08 pm
Hi,
I'm trying to update a table which contain these fields : ItemID, InventoryID, total amounts
with a query that return these values itemId, inventoryid and total amounts for each items
Mind you, not all the rows in the table need to be updated. only a few.
This what i wrote but doesn't work since the query return multiple values so i can't assign it to journalAmounts.
UPDATE [bmssa].[etshortagetemp]
SET JournalAmounts = (SELECT sum(b.BomQty) FROM [bmssa].[Bom] b
JOIN [bmssa].[SalesLine] sl ON sl.ItemBomId = b.BomId
JOIN [bmssa].[SalesTable] st ON st.SalesId = sl.SalesId
WHERE st.SalesType = 0 AND (st.SalesStatus IN (0,1,8,12,13)) AND st.DataAreaId = 'sdi'
GROUP BY b.itemid, b.inventdimid)
Any advise how to do this task?
October 12, 2006 at 4:56 am
Hi Eddy,
since you didn't post table definitions and their relationships, I can only guess what columns are in table etshortagetemp and how to use them to update the correct rows. Generally said, you can do this using derived table.
This is just an example how you could do it, if table etshortagetemp has columns itemid and inventdimid, and if these are enough to pinpoint the correct row(s) to be updated (derived table definition is in bold):
UPDATE et
SET JournalAmounts = Q.thesum
FROM [bmssa].[etshortagetemp] et
JOIN
(SELECT b.itemid, b.inventdimid, sum(b.BomQty as thesum)
FROM [bmssa].[Bom] b
JOIN [bmssa].[SalesLine] sl ON sl.ItemBomId = b.BomId
JOIN [bmssa].[SalesTable] st ON st.SalesId = sl.SalesId
WHERE st.SalesType = 0 AND (st.SalesStatus IN (0,1,8,12,13)) AND st.DataAreaId = 'sdi'
GROUP BY b.itemid, b.inventdimid) AS Q
ON Q.itemid=et.itemid AND Q.inventdimid=et.inventdimid
If this didn't help, please post table definitions and describe how you decide which row to update.
October 12, 2006 at 8:45 am
Thanks Vladan,
You got it right on. Thats what i want to do.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply