Sql update statement says an error

Question

Iam updating an temporary table from the original table but it is having some issues can anyone help me for this problem here is my code and the error

The below given statement says Incorrect syntax near the keyword 'group'.

Update #tmpODU set Tn1Cnt=1,TnPay=isnull(sum(Amt),0)  
From Table1 
where Code COLLATE DATABASE_DEFAULT=TcCode 
COLLATE DATABASE_DEFAULT and Del='R' and Date<=dateadd(M,@n1D,@dDate) 
group by Code

can anyone help me...

thank you.....

Your SQL request is complete garbage. You mix SELECT with UPDATE — user4035, Jan 10 '14 at 05:32
It will helpful `http://stackoverflow.com/questions/11909786/t-sql-update-with-sum-and-group-by` — Amit, Jan 10 '14 at 05:34
select statement may return multiple rows which can not be handled by update statement. — Raghuveera, Jan 10 '14 at 05:39

score 0 · Answer 1 · edited Jan 10 '14 at 07:02

i got the answer using below statment

and it is working as i expected

Update #tmpODU 
   set Tn1Cnt=1,
       Tn1Pay=isnull((select sum(Amt) 
                      From table1
                      where Code COLLATE DATABASE_DEFAULT=TcCode COLLATE DATABASE_DEFAULT 
                      and Del='R' 
                      and Date<=dateadd(M,@n1D,@dDate) group by Code),0)

Sql update statement says an error

1 Answers1