CODEWITHSUNDEEP
phpmysql
Wei Keat
Wei Keat

Reputation: 676

How to bulk update mysql data with one query?

$query = mysql_query("UPDATE a SET fruit = '**apple**' WHERE id = '**1**' ");
$query2 = mysql_query("UPDATE a SET fruit = '**orange**' WHERE id = '**2**' ");
$query3 = mysql_query("UPDATE a SET fruit = '**peach**' WHERE id = '**3**' ");

is there any way to simplify it to one query?

Upvotes: 48

Views: 76632

Answers (4)

odunayo12
odunayo12

Reputation: 585

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.

Upvotes: 3

skumar
skumar

Reputation: 151

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

Upvotes: 2

Omesh
Omesh

Reputation: 29121

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

Upvotes: 66

Yaroslav
Yaroslav

Reputation: 2438

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.

Upvotes: 68

Related Questions