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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy