Update using multiple aggregate values 1

[mikelawrence]

I want to update 3 fields in a table of summarised data derived from another table. The update below works fine if i update just one field but any ideas how i can update more than 1 field at a time?

update top100
set jantotaltransactions
= (select count(t.memberid)
from pts_transactions t
where t.memberid = top100.memberid
and t.outletcode = top100.outletcode
group by t.memberid,t.outletcode)

and in principle what i want to do is

update top100
set jantotaltransactions, jantotalspend
= (select count(t.memberid), sum(t.spend)
from pts_transactions t
where t.memberid = top100.memberid
and t.outletcode = top100.outletcode
group by t.memberid,t.outletcode)

but it doesn't work. I'd appreciate any advice!

thanks

mike

 

[RiverGuy]

There are a variety of ways. Here's one

UPDATE top100
SET jantotaltransactions = b.totaltransactions,
jantotalspend = b.totalspend
FROM top100, 
  (SELECT memberid, outletcode,
   COUNT(memberid) AS totaltransactions,
   SUM(spend) AS totalspend
   FROM pts_transactions
   GROUP BY memberid, outletcode) b
WHERE top100.memberid = b.memberid
AND top100.outletcode = b.outletcode