MySQL - I Want to Update Multiple Rows on the bases of select query without using PHP

Question

I want to execute update query like this.

UPDATE reg_form set wallet_balance in("10","22","25") where id in("1","2","3");

I know the Syntax of Update is: UPDATE reg_form set wallet_balace = '10' where ...... but I want to execute query which is highlighted.

Here, Wallet Balance should be updated on the basis of id.

Edit:-

Here I have put the syntax for example only. In real Scenario I am getting amount from another table and the sum of that on basis of user_id updating in Table.

Like,

Update reg_form set wallet_balance in (SELECT refund_amount from refund) where user_id in (SELECT user_id from refund);

So, here I am expecting dynamic behaviour. Many users will be refunded as per their ID without manually checking cases.

What's your question about this? What is "query like this" - is there any dynamic part in it? — Nico Haase, Jul 08 '20 at 09:39
My question is about dynamic behaviour of table. If today there are 20 users so maybe tomorrow it maybe 100. So, I want to refund all users of their refund amount on the basis of user id with single query. But we all know that in update query we can only set one fixed amount. I want that set on the basis of user id — rahul g, Jul 10 '20 at 15:34
Please add all such explanation to your question by editing it — Nico Haase, Jul 10 '20 at 15:35

Felix Eklöf · Accepted Answer · 2020-07-09T08:46:51.227

0

You could simply do it in multiple queries if I understood your question correct. These can all be executed at the same time.

UPDATE reg_form SET wallet_balance = '10' WHERE id = 1;
UPDATE reg_form SET wallet_balance = '22' WHERE id = 2;
UPDATE reg_form SET wallet_balance = '25' WHERE id = 3;

EDIT

Does this answer your follow up question? UPDATE query based on SELECT Query

You would do something like:

UPDATE reg_form AS a
INNER JOIN refund AS b ON a.id = b.id
SET a.wallet_balance = b.refund_amount

edited Jul 09 '20 at 08:46

answered Jul 08 '20 at 09:30

Felix Eklöf

3,253
2
10
27

I have just edited the Question, can you please Improve your answer according to it? – rahul g Jul 08 '20 at 11:22
Actually I am running it in MySQL event scheduler. I know it supports multiple queries but I want to do it with single one. – rahul g Jul 10 '20 at 15:36
My second query is just one single query. – Felix Eklöf Jul 10 '20 at 15:43
Thanks, I will it try and let you know. – rahul g Jul 10 '20 at 15:47

score 0 · Answer 2 · answered Jul 08 '20 at 09:35

0

You can do it with a CASE expression:

UPDATE reg_form 
SET wallet_balance = CASE id
  WHEN '1' THEN '10'
  WHEN '2' THEN '22'
  WHEN '3' THEN '25'
END

answered Jul 08 '20 at 09:35

forpas

160,666
10
38
76

I have just edited the Question, can you please Improve your answer according to it? – rahul g Jul 08 '20 at 11:22
This code answers the question as you posted it. If you have a different requirement then you can post a new question. – forpas Jul 08 '20 at 11:23
This post is absolutely correct if I want to verify all the cases manualy with when. But think about it, there might be multiple users everyday, not fixed numbers. So, how can I verify all that things with your answer. – rahul g Jul 10 '20 at 15:39
This is not how it works here in SO. When you ask a question you get answers for the requirement that you mentioned in the question. If you change the requirement then you invalidate all the answers that you got. – forpas Jul 10 '20 at 15:45
Sorry, I'm new to this platform. Just learning about this. – rahul g Jul 10 '20 at 15:48

MySQL - I Want to Update Multiple Rows on the bases of select query without using PHP

2 Answers2