how to execute the batch query in mysql for update column values

Question

For example i have a table ACCOUNT WITH TWO COLUMNS, ID AND A_NUMBER. AND I WANT TO UPDATE THE A_NUMBER VALUES IN A SINGLE QUERY WITH DIFFRENT VALUES. "UPDATE ACCOUNT SET A_NUMBER="0135" WHERE ID=131; UPDATE ACCOUNT SET A_NUMBER="0145" WHERE ID=132; UPDATE ACCOUNT SET A_NUMBER="0155" WHERE ID=133; UPDATE ACCOUNT SET A_NUMBER="0160" WHERE ID=134; UPDATE ACCOUNT SET A_NUMBER="0175" WHERE ID=135; UPDATE ACCOUNT SET A_NUMBER="0180" WHERE ID=136; UPDATE ACCOUNT SET A_NUMBER="0195" WHERE ID=137; UPDATE ACCOUNT SET A_NUMBER="0200" WHERE ID=138;"

WEBProject · Accepted Answer · 2010-07-03 05:41:58Z

3

You should use "CASE" Statment:

UPDATE tbl SET A_NUMBER = CASE
WHEN id = 1 THEN ‘0135’
WHEN id = 2 THEN ‘0175’
...
END
WHERE id IN (1,2,...)

Hope i helped

answered Jul 3, 2010 at 5:41

WEBProject

1,3457 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to execute the batch query in mysql for update column values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related