Please help on SQL update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help on SQL update statement

Author

Topic

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-20 : 10:27:21

I want to update recap table for monthly sales
table already inserted with Item-code, I need to update SalesQty and SalesAmt like this:

update tbRecapSales set
SalesQty = sum(d.Qty), SalesAmt = sum(d.Qty*d.Price)
from tbDetailSales d
inner join tbHeaderSales h on d.[Sales Nr] = h.[Sales Nr]
inner join tbRecapSales r on d.Item = r.item
where h.[Sales Date] between '1/1/2008' and '1/31/2008'
group by d.Item

when I execute this SQL, I got error
An aggregate may not appear in the set list of an UPDATE statement.

whats wrong with my coding, please help
Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 10:50:54

[code]update x
set x.SalesQty = d.Qty,
x.SalesAmt = d.Price
from tbRecapSales AS x
INNER JOIN (
SELECT d.item,
sum(d.qty) AS qty,
sum(d.qty * d.price) AS price
from tbDetailSales AS d
inner join tbHeaderSales h on d.[Sales Nr] = h.[Sales Nr]
inner join tbRecapSales r on d.Item = r.item
where h.[Sales Date] >= '1/1/2008'
and h.[Sales Date] < '2/1/2008'
group by d.Item
) AS g ON g.Item = x.Item[/code]

E 12°55'05.25"
N 56°04'39.16"

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-20 : 10:51:27

[code]UPDATE tbRecapSales
SET SalesQty = a.SalesQty,
SalesAmt = a.SalesAmt
FROM tbRecapSales r
INNER JOIN ( SELECT d.Item,
SUM(d.Qty) AS SalesQty,
SUM(d.Qty * d.Price) AS SalesAmt
FROM tbDetailSales d
INNER JOIN tbHeaderSales h ON d.[Sales Nr] = h.[Sales Nr]
WHERE h.[Sales Date] BETWEEN '1/1/2008' AND '1/31/2008'
GROUP BY d.Item
) a ON r.Item = a.Item[/code]

JasonAnt
Starting Member

23 Posts

Posted - 2008-02-20 : 11:05:13

Its Work !!

Thank you very much for Both of you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources