0
$sql = "UPDATE purchase SET stat='1' WHERE `id`='$id' AND 4*(plan)<= sum(plan) ";

Or

$sql = "UPDATE purchase SET stat='1' WHERE `id`='$id' AND 4*(plan-1)<= sum(plan-1) ";

I am trying to make a query by this update, if summation of all row is grater than 1st row, it will update 1st row, and this rules for all row. But it is showing error and says 

Error updating record: Invalid use of group function

What is the problem in this query? I need help for this error.

Salim Murshed
  • 1,423
  • 1
  • 8
  • 19
  • 1
    _Invalid use of group function_ pretty much covers it; you can't do that directly in an UPDATE; you can maybe try using a subquery, or (if that doesn't work) query for it in a separate query first. _The subquery option might not work without some hokey tricks, like wrapping the subquery in a SELECT * subquery; so I would lean toward the latter option._ – Uueerdo Jun 19 '18 at 20:41
  • https://stackoverflow.com/questions/9253244/sql-having-vs-where – Devon Bessemer Jun 19 '18 at 20:42
  • Can you give me an example by editing this? Thanks – Salim Murshed Jun 19 '18 at 20:43
  • @Devon doubt that'll help here; he is trying to use aggregation in an UPDATE query. – Uueerdo Jun 19 '18 at 20:43

1 Answers1

2

You can't use an aggregate function like SUM() in the WHERE clause of a query, because SUM() is calculated over all the selected rows, but WHERE is used to select the rows. You need to calculate the sum in a separate query, then join it.

UPDATE purchase
JOIN (SELECT SUM(plan) AS plantotal FROM purchase) AS p1
    ON 4*(plan-1) <= plantotal
SET stat = '1'
WHERE id = '$id'

To base your update on all the rows with higher IDs, you can join with a subquery that gets totals for each group of rows.

UPDATE purchase AS p
JOIN (SELECT p1.id AS id, SUM(p2.plan) AS plantotal
      FROM purchase AS p1
      JOIN purchase AS p2 ON p2.id > p1.id) AS p3
ON p3.id = p.id AND 4*(p.plan-1) <= p3.plantotal
SET stat = '1'
Barmar
  • 741,623
  • 53
  • 500
  • 612