How to bulk update mysql data with one query?
Yes you can do it using this query:
UPDATE a
SET fruit = (CASE id WHEN 1 THEN 'apple'
WHEN 2 THEN 'orange'
WHEN 3 THEN 'peach'
END)
WHERE id IN(1,2 ,3);
Based on the warning message
'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
One may consider a slight modification of Yaroslav's solution like so:
INSERT into `table` (id,fruit)
VALUES (1,'apple'), (2,'orange'), (3,'peach') as tb
ON DUPLICATE KEY UPDATE fruit = tb.fruit;
It does just the same thing but mutes the warning message.
I found a following solution:
INSERT into `table` (id,fruit)
VALUES (1,'apple'), (2,'orange'), (3,'peach')
ON DUPLICATE KEY UPDATE fruit = VALUES(fruit);
Id must be unique or primary key. But don't know about performance.
Using IF() function in MySQL this can be achieved as
UPDATE a
SET fruit = IF (id = 1, 'apple', IF (id = 2, 'orange', IF (id = 3, 'peach', fruit)));