July 20, 2010 at 2:21 pm
Can someone please help me out? I have a table called TempTotal with columns LotNumber and ShipQty.
The LotNumber column is already populated; I just need to add total ShipQty per LotNumber. This is what I have:
UPDATE TempTotal
SET ShipQty = (select sum(ShippedQuantity) from FS_HistoryShipment
group by LotNumber)
I get the message: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
If I try something else I get the other message: An aggregate may not appear in the set list of an UPDATE statement.
Any help would be greatly appreciated.
July 20, 2010 at 2:35 pm
sdownen05 (7/20/2010)
Can someone please help me out? I have a table called TempTotal with columns LotNumber and ShipQty.The LotNumber column is already populated; I just need to add total ShipQty per LotNumber. This is what I have:
UPDATE TempTotal
SET ShipQty = (select sum(ShippedQuantity) from FS_HistoryShipment
group by LotNumber)
I get the message: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
is it something like this?
update TempTotal a
set a.ShipQty = (select sum(ShippedQuantity)
from FS_HistoryShipment b
where a.LotNumber = b.LotNumber
group by b.LotNumber)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2010 at 2:43 pm
That worked, except I had to take out the 'a'. I got a message saying "Incorrect syntax near 'a'". This is what it looks like now:
update TempTotal
set ShipQty = (select sum(ShippedQuantity)
from FS_HistoryShipment b
where TempTotal.LotNumber = b.LotNumber
group by b.LotNumber)
Thank you very much for your help. Very much appreciated.
July 21, 2010 at 6:07 am
sdownen05 (7/20/2010)
That worked, except I had to take out the 'a'. I got a message saying "Incorrect syntax near 'a'". This is what it looks like now:update TempTotal
set ShipQty = (select sum(ShippedQuantity)
from FS_HistoryShipment b
where TempTotal.LotNumber = b.LotNumber
group by b.LotNumber)
Thank you very much for your help. Very much appreciated.
You are welcome. Glad to help.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 21, 2010 at 6:18 am
Instead of using a correlated subquery, you might want to look into using a derived table:
UPDATE a
SET a.ShipQty = b.ShippedQuantity
FROM TempTotal a
JOIN (SELECT LotNumber, ShippedQuantity = sum(ShippedQuantity)
FROM FS_HistoryShipment
GROUP BY LotNumber) b
ON a.LotNumber = b.LotNumber
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply