CODEWITHSUNDEEP
mysql
Yaseen Hussain
Yaseen Hussain

Reputation: 62

MYSQL UPDATE query using ORDER BY and LIMIT on a JOIN statement

I'm trying to do a UPDATE query which will SUM the rewards_balance of only 5 users sorted by the date of their purchase.

Users table is :

id     rewards_balance
1      0
2      20
3      0
4      23
5      11
6      45
7      1
8      0
9      0 
10     10

Here is my subscription table

payer_id     created
1           1616137161
2           1616137165
5           1616137166
7           1616137167
3           1616137168
4           1616137169
9           1616137175

The UPDATE query should work in such a way that it will add a set amount to 5 users who have had the subscription first. So the output after UPDATE should be as follows

id     rewards_balance
1      0+20
2      20+20
3      0+20
4      23
5      11+20
6      45
7      1+20
8      0
9      0 
10     10

So as you see, the 20 is added to only those IDs who have their subscription first.

What I have tried so far is :

UPDATE users u LEFT JOIN subscription m ON u.id = m.payer_id SET u.rewards_balance = u.rewards_balance+20
ORDER BY `m`.`created` ASC LIMIT 10

I keep getting the following error.

Query error: Incorrect usage of UPDATE and ORDER BY

Assist please.

Upvotes: 0

Views: 105

Answers (1)

slaakso
slaakso

Reputation: 9050

ORDER BY and LIMIT are not allowed in multi-table UPDATE. See documentation.

You can do this instead:

UPDATE users u 
JOIN (
  SELECT m.payer_id
  FROM subscription m
  ORDER BY `m`.`created`
  LIMIT 10
) q on q.payer_id=u.id
SET u.rewards_balance = u.rewards_balance+20;

Upvotes: 1

Related Questions