48
$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?

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
Wei Keat
  • 676
  • 2
  • 9
  • 12

4 Answers4

66

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);
patrick
  • 11,519
  • 8
  • 71
  • 80
Omesh
  • 27,801
  • 6
  • 42
  • 51
  • `case id when 1 then 'apple' when 2 then ...` is an alternative. – mu is too short Jul 26 '12 at 07:54
  • 6
    I just wanted to mention an observation that the CASE statement will test each and every case until a condition is satisfied. When id is 3, 1 and 2 will be tested before being true. For that reason alone I went with @Yaroslav's approach where the condition of a duplicate key sets the value without further conditionals applied. – Robert Brisita Mar 11 '15 at 18:26
66

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.

Yaroslav
  • 2,338
  • 26
  • 37
  • This works for me. I wonder why mysql did not implement a bulk update statement. – Christian Noel Jan 16 '17 at 01:50
  • 6
    Ideal solution for some use cases, but this will not work if `table` has a required column `b` which has no default value, even if the records which are being updated have already defined this value. Updating only parts of an existing record requires a different approach. – Jacob Crofts Apr 29 '17 at 04:41
  • 7
    Should be aware that this way auto_increment still grows for each unsuccessful insert statement attempt – George G Sep 11 '17 at 16:07
1

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)));
skumar
  • 141
  • 2
1

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.

odunayo12
  • 425
  • 5
  • 10