CODEWITHSUNDEEP
phpmysql
Salim Murshed
Salim Murshed

Reputation: 1451

Error updating record: Invalid use of group function for updating previous all data

$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.

Upvotes: 0

Views: 513

Answers (1)

Barmar
Barmar

Reputation: 781350

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'

Upvotes: 2

Related Questions