Update Multiple Rows At Once With Different Values in MySQL

I’ve had to figure this out on my own twice now, so I guess it’s time to document it. It is possible, and fairly easy, to update multiple rows of a MySQL table at the same time, with different values for each row. Unfortunately it’s not as easy as inserting, but once you see what’s being done you will probably say “Oh, of course!”.

The key to the multiple row update query is the CASE statement.  MySQL’s CASE reference page doesn’t even have the word “UPDATE” on the page anywhere, but don’t let that fool you, it can be used in UPDATE statements!

The resultant query, to all of you holding your breath, will look something like this:

UPDATE `table` SET

`column1` = CASE
WHEN `id`='1034786' THEN '0'
WHEN `id`='1037099' THEN '0'
WHEN `id`='1034789' THEN '3'
ELSE `column1` END,

`column2` = CASE
WHEN `id`='1034786' THEN NULL
WHEN `id`='1037099' THEN '1034789'
WHEN `id`='1034789' THEN '1034789'
ELSE `column2` END,

`column3` = CASE
WHEN `id`='1034786' THEN 'Text One'
WHEN `id`='1037099' THEN 'Text Two'
WHEN `id`='1034789' THEN 'Text Three'
ELSE `column3` END

WHERE `id`='1034786' OR `id`='1037099' OR `id`='1034789'

As you can see, for each column to be update we create a CASE statement which updates the column based on a unique ID. As I said, it’s not as easy as inserting, but if you have ever seen a case or switch statement while programming it should be straight forward. The ELSE case will let us keep the original value if we don’t explicitly provide one. The WHERE will limit our updates to the rows we are trying to update.

Now, for a little untested PHP code similar to something I’ve been working on:

// Update values we got from somewhere
$update_values = Array(
  '1034786' => Array('column1' => 0, 'column2' => NULL, 'column3'=> 'Text One'),
  '1037099' => Array('column1' => 0, 'column2' => 1034789 , 'column3'=> 'Text Two'),
  '1034789' => Array('column1' => 3, 'column2' => 1034789 , 'column3'=> 'Text Three')
);

// Start of the query
$update_query = "UPDATE `table` SET ";

// Columns we will be updating
$columns = Array('column1' => '`column1` = CASE ', 'column2' => '`column2` = CASE ', 'column3' => '`column3` = CASE ');

// Build up each columns CASE statement
foreach($update_values as $id => $values){
  $columns['column1'] .= "WHEN `id`='" . mysql_real_escape_string($id) . "' THEN '" . mysql_real_escape_string($values['column1']) . "' ";
  $columns['column2'] .= "WHEN `id`='" . mysql_real_escape_string($id) . "' THEN "  . ($values['column2'] === NULL ? "NULL" : "'".mysql_real_escape_string($values['column1'])."'") . " ";
  $columns['column3'] .= "WHEN `id`='" . mysql_real_escape_string($id) . "' THEN '" . mysql_real_escape_string($values['column3']) . "' ";
}

// Add a default case, here we are going to use whatever value was already in the field
foreach($columns as $column_name => $query_part){
  $columns[$column_name] .= " ELSE `$column_name` END ";
}

// Build the WHERE part. Since we keyed our update_values off the database keys, this is pretty easy
$where = " WHERE `id`='" . implode("' OR `id`='", array_keys($update_values)) . "'";

// Join the statements with commas, then run the query
$update_query .= implode(', ',$columns) . $where;
mysql_query($update_query) or die(mysql_error());

That aught to be enough to either get you started, or get you in trouble.

As always, make a backup of your database or use a test database when testing UPDATE queries.

Enjoy!

This entry was posted in Programming and tagged , , , , . Bookmark the permalink.

8 Responses to Update Multiple Rows At Once With Different Values in MySQL

  1. Jules Manson says:

    Very good post. I was looking for something exactly like this. My PHP is fairly good so I didn’t need the PHP part but I definitely needed to see how to write a MySQL statement that does exactly this.

    May I ask, do you know or use MySQLi prepared statements? If so I would love to see an article exactly like this using prepared statements. I find writing the php for MySQLi prep statements a bit tricky.

    Thanks again for a great post.

    Jules

  2. stuporglue says:

    I’m happy that this was helpful to you!

    “May I ask, do you know or use MySQLi prepared statements? If so I would love to see an article exactly like this using prepared statements.”

    I’m aware of prepared statements, but I haven’t actually used them. Sorry!

  3. Hi,

    You are a genious been looking for something like this.

    Really helpful and kind of you to put an example of such.

    I have put the PHP code around it and works a treat. :-)

    Thanks

    Steve

  4. anon says:

    Thank you so much for writing this, this is literally the only article I could find anywhere that spelled it out simply.

    I wasn’t quite sure about the ELSE parts though. For example you put

    `column3` = CASE
    WHEN `id`='1034786' THEN 'Text One'
    WHEN `id`='1037099' THEN 'Text Two'
    WHEN `id`='1034789' THEN 'Text Three'
    ELSE `title` END

    Surely it should be ELSE `column3` END ?

  5. ritu raj says:

    wowww….it was so worth reading ….it really helped me 2 imagine even more in mysql..

    :-))))))))))

    thankksss a lot!!!!!!!!!!!!

  6. This was super helpful!!

    Thanks for taking the time to write this up!

  7. Ritwik says:

    You’re nothing short of being a genius.

Leave a Reply

Your email address will not be published. Required fields are marked *