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;"

Answer 1

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