April 16, 2015 at 10:42 pm
Hi guys,
I'm getting the following error message 'An aggregate may not appear in the set list of an
UPDATE statement' What is the proper way to carry out an update on aggregates?
My code is below:
t1.TotalTest1 = coalesce(Sum(t2.AmountTest1), 0.00),
t1.TotalTest2 = coalesce(Sum(t2.AmountTest2), 0.00),
t1.TotalTest3 = coalesce(Sum(t2.AmountTest3), 0.00),
t1.TotalTest4 = coalesce(Sum(t2.AmountTest4), 0.00),
from #tbl_CHA t1
inner join
(
select
tt1. AmountTest1,
tt1. AmountTest2,
tt1. AmountTest3,
tt1. AmountTest4
from Test.dbo.Posted tt1 (nolock)
union all
select
tt1. AmountTest1,
tt1. AmountTest2,
tt1. AmountTest3,
tt1. AmountTest4
from Test.dbo.Pended tt1 (nolock)
union all
select
tt1. AmountTest1,
tt1. AmountTest2,
tt1. AmountTest3,
tt1. AmountTest4
from Test.dbo.PrePay tt1) as t2 on
t1.Id = left(t2.Id_1, 15) + right(t2.Id_1, 2)
April 17, 2015 at 2:17 am
You're missing some part of the statement, I'll assume it's just the UPDATE part at the top.
Here's how you do it:
UPDATE t1
SET t1.TotalTest1 = coalesce(t2.SumAmountTest1, 0.00)
,t1.TotalTest2 = coalesce(t2.SumAmountTest2, 0.00)
,t1.TotalTest3 = coalesce(t2.SumAmountTest3, 0.00)
,t1.TotalTest4 = coalesce(t2.SumAmountTest4, 0.00)
FROM #tbl_CHA t1
INNER JOIN (
SELECT Sum(t2.AmountTest1) AS SumAmountTest1,
Sum(t2.AmountTest2) AS SumAmountTest2,
Sum(t2.AmountTest3) AS SumAmountTest3,
Sum(t2.AmountTest4) AS SumAmountTest4
FROM (
SELECT tt1.AmountTest1
,tt1.AmountTest2
,tt1.AmountTest3
,tt1.AmountTest4
FROM Test.dbo.Posted tt1 --(NOLOCK) get rid of NOLOCK!
UNION ALL
SELECT tt1.AmountTest1
,tt1.AmountTest2
,tt1.AmountTest3
,tt1.AmountTest4
FROM Test.dbo.Pended tt1 --(NOLOCK) get rid of NOLOCK!
UNION ALL
SELECT tt1.AmountTest1
,tt1.AmountTest2
,tt1.AmountTest3
,tt1.AmountTest4
FROM Test.dbo.PrePay tt1
) AS t
) AS t2 ON t1.Id = left(t2.Id_1, 15) + right(t2.Id_1, 2)
BTW, get rid of NOLOCK: it's not a "go faster" option.
-- Gianluca Sartori
April 17, 2015 at 8:02 am
Thanks for the insight. However, I'm getting the following error message below:
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'From'.
Msg 156, Level 15, State 1, Line 67
Incorrect syntax near the keyword 'As'.
AS t2 ON t1.Id = left(t2.Id_1, 15) + right(t2.Id_1, 2)
spaghettidba (4/17/2015)
You're missing some part of the statement, I'll assume it's just the UPDATE part at the top.Here's how you do it:
UPDATE t1
SET t1.TotalTest1 = coalesce(t2.SumAmountTest1, 0.00)
,t1.TotalTest2 = coalesce(t2.SumAmountTest2, 0.00)
,t1.TotalTest3 = coalesce(t2.SumAmountTest3, 0.00)
,t1.TotalTest4 = coalesce(t2.SumAmountTest4, 0.00)
FROM #tbl_CHA t1
INNER JOIN (
SELECT Sum(t2.AmountTest1) AS SumAmountTest1,
Sum(t2.AmountTest2) AS SumAmountTest2,
Sum(t2.AmountTest3) AS SumAmountTest3,
Sum(t2.AmountTest4) AS SumAmountTest4
FROM (
SELECT tt1.AmountTest1
,tt1.AmountTest2
,tt1.AmountTest3
,tt1.AmountTest4
FROM Test.dbo.Posted tt1 --(NOLOCK) get rid of NOLOCK!
UNION ALL
SELECT tt1.AmountTest1
,tt1.AmountTest2
,tt1.AmountTest3
,tt1.AmountTest4
FROM Test.dbo.Pended tt1 --(NOLOCK) get rid of NOLOCK!
UNION ALL
SELECT tt1.AmountTest1
,tt1.AmountTest2
,tt1.AmountTest3
,tt1.AmountTest4
FROM Test.dbo.PrePay tt1
) AS t
) AS t2 ON t1.Id = left(t2.Id_1, 15) + right(t2.Id_1, 2)
BTW, get rid of NOLOCK: it's not a "go faster" option.
April 17, 2015 at 8:06 am
It's working for me, it must be something else.
BTW, the script has only 35 lines and it's complaining about an error at line 67, so maybe you're not showing us the whole picture.
-- Gianluca Sartori
April 17, 2015 at 9:33 am
I don't understand how this code can work.
Your subqueries to tt1 / t2 don't contain any column named "Id_1" yet you reference that column when you join to t2: I don't understand how SQL resolves that reference.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply