0

Can Someone please help me where is the issue am getting 'invalid use of group function' in below query 

UPDATE t1 
JOIN t2 ON t1.id=t2.id
SET t1.total_amount = SUM(IF((t2.`due` <= 0), t2.`amount`, 0))
WHERE t2.flag=1 AND t2.id=003;
GMB
  • 216,147
  • 25
  • 84
  • 135
NagTej
  • 3
  • 1
  • Use a subquery in the SET clause or a JOIN with a derived table. Look at [how-to-update-fields-that-is-the-aggregate-result-of-another-table-in-mysql](https://stackoverflow.com/questions/5752075/how-to-update-fields-that-is-the-aggregate-result-of-another-table-in-mysql) – Paul Spiegel Jan 18 '20 at 12:44

1 Answers1

0

You want to aggregate in a subquery, then join and set in the outer query:

update t1
inner join (
    select id, sum(case when due < 0 then t2.amount else 0 end) total_amount
    from t2
    where id = 3 and flag = 1
    group by id
) t2 on t1.id = t2.id
set t1.total_amount = t2.total_amount
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Note that the two queries are not equivalent. If there is no match in `t2`, the first query will do nothing, while the second query will set `total_amount` to NULL. – Paul Spiegel Jan 18 '20 at 12:50
  • @PaulSpiegel: ah yes, you are correct, thanks. Let me remove the second query from my answer then. – GMB Jan 18 '20 at 12:52