How to use sum in SQL Update query.

[mur3x]

How to use sum in SQL Update query.

Hi i want to achieve the following,
which is, the paidamt field in Student table shud be
equal to Sum of installment field (in table payments).
I wrote the following SQL but why it doesn't work..

cn.Execute "update students as s,payment as p 
set s.paidamt=s.paiadmt+sum(p.installment) 
where s.regno=p.regno and s.regno=" & lngRegNo

thnx!
Mur3.

 

[Golom]

It probably didn't work because you have an aggregate function (SUM) in your field definition but you have no grouping information. If you try to use a GROUP BY clause on this it will probably still fail because, in general, queries containing GROUP BY are not updatable. Instead you might try something like

cn.Execute
"UPDATE students as S
SET S.paidamt = S.paiadmt + (SELECT SUM(P.installment) From Payment As P Where P.RegNo = S.Regno )
WHERE S.regno=" & lngRegNo

 

[mur3x]

well been awhile since i checked it out.
anyways it d'nt work out for me..
thnx anyway, for ur idea!!!

so i turned to the more easier way...
using a recordset & a variable.

rs4.Open "SELECT sum(p.installment) as TotalIns FROM Payment AS p where p.regno=" & lngRegno

varTotins = rs4("Totalins")

cn.Execute "update students as s set s.paidamt=" & varTotins & " where s.regno=" & lngRegno

[code]

Mur.

 

[MarkSweetland]

Can't have an aggregate in the set statement of the query, but you can have one in a subquery:

strSQL + " update students " & _ 
  " set paidamt= paidamt +  " & _ 
  " (select sum(isNull(payment.installment,0))" & _ 
  " from payment " & _ 
  " where students.regno=payment.regno and  " & _ 
  " student.regno=" & lngRegno & ")"

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach