SQL - GROUP BY

You can't update more than a single table once. You will have to do them separately.

I'm actually only updating one table (prtrans). I thought the code required the others since they are referanced. It works when I do the same thing but use amount and not SUM(amount)

At this part you only give the name of the table which you need to update
UPDATE afn, po, pr, prtrans


so, suppose you have two tables and both have id field in common , and you want to update name of table1 with surname of table2 then
update t1
set t1.name = t2.surname
from table1 t1
inner join table2 t2
on t1.id = t2.id

I can find textbook examples myself, I need a real solution. What you suggest has nothing to do with a GROUP BY and SUM.

try

update 	prtrans, 
	(
	select afn.prtrans_id, sum(afn.amount) as samount
	from afn
	where afn.appr_date like '2011%' and afn.astatus = 'Approved'
	group by afn.prtrans_id
	) b, 
	po, pr
set a.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '') + b.samount
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND b.prtrans_id = prtrans.prtrans_id;

Select allOpen in new window

oops typo

update 	prtrans, 
	(
	select afn.prtrans_id, sum(afn.amount) as samount
	from afn
	where afn.appr_date like '2011%' and afn.astatus = 'Approved'
	group by afn.prtrans_id
	) b, 
	po, pr
set prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '') + b.samount
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND b.prtrans_id = prtrans.prtrans_id;

Select allOpen in new window

If this is MySQL, then you will want to have the JOIN in the UPDATE like so:

UPDATE a JOIN b ON a.id = b.id
SET ...

(or like you have it above using the UPDATE a, b SET ... WHERE a.id = b.id format)

To include your GROUP BY and SUM, simply derive that portion of the query.
i.e., alter the above as:
UPDATE a
JOIN (
   SELECT id, SUM(somevalue) AS SumOfValue
   FROM b
   GROUP BY id  
) b ON b.id = a.id
SET a.somevalue = b.SumOfValue
;

Hope that makes sense!

It looks like ralmada posted while I was still typing and it appears he used your actual tables, so hopefully that helps you. My post was to help you understand how it works: http:#37345784 ; therefore, hopefully, you find it useful for future needs. Good luck!

Its starting to make sense and yese its MySQL 5.5

UPDATE       prtrans
JOIN      (
      SELECT afn.prtrans_id, SUM(afn.amount) AS samount
      FROM afn
      WHERE afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved'
      GROUP BY afn.prtrans_id
      )
      po, pr
SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;UPDATE       prtrans
JOIN      (
      SELECT afn.prtrans_id, SUM(afn.amount) AS samount
      FROM afn
      WHERE afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved'
      GROUP BY afn.prtrans_id
      )
      po, pr
SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;

now I get the error.....
Error Code: 1054
Unknown column 'po.pr_id' in 'where clause'

Yet there is a pr_id field in the po table?

Sorry it got pasted twice...

UPDATE       prtrans
JOIN      (
      SELECT afn.prtrans_id, SUM(afn.amount) AS samount
      FROM afn
      WHERE afn.appr_date LIKE '2011%' AND afn.astatus = 'Approved'
      GROUP BY afn.prtrans_id
      )
      po, pr
SET prtrans.R_AMT_CY_T = IF ((pr.pstatus = 'Submitted' OR (po.appr_date LIKE '2011%' AND po.po_status != 'Cancelled')), prtrans.amount, '')
WHERE pr.pr_id = prtrans.pr_id
AND pr.pr_id = po.pr_id
AND afn.prtrans_id = prtrans.prtrans_id;

Thanks, it works :)