MySQL: Update Multiple Rows or Records with One Single Query

by Yang Yang on May 17, 2009

As MySQL doesn’t have inherent support for updating more than one rows or records with a single update query as it does for insert query, in a situation which needs us to perform updating to tens of thousands or even millions of records, one update query for each row seems to be too much.

Reducing the number of SQL database queries is the top tip for optimizing SQL applications.

So, is there any other way around with just a few MySQL queries that  equals to millions of single row update queries?

Yes, but you need a temporary table.

Step 1: Create a temporary table

This table should have 2 columns: 1) an ID column that references the original record’s primary key in the original table, 2) the column containing the new value to be updated with.

Creating this table, you can use insert queries as it conveniently inserts more than one rows at a time (with a single query) – actually, as many rows as you need, like 1000 a time.

Step 2: Transfer the new values from the temporary table to the intended table

Use the query below:

UPDATE original_table, temp_table SET original_table.update_column = temp_table.update_column WHERE original_table.id = temp_table.original_id

To transfer all the new values you have in the temporary table to the original table. After this, you have successfully updated the original table with much much less than a million queries, probably just a hundred or so.

UPDATE =============================================

So here’s perhaps a better idea in most of the cases, use the CASE WHEN ELSE END syntax of MySQL to update multiple rows in the options table of your web application:

UPDATE `options` SET `value` = CASE `name`
WHEN 'site_name' THEN 'My Blog'
WHEN 'site_url' THEN 'http://www.example.com'
WHEN 'site_email' THEN 'sam@example.com'
ELSE `value`
END

Make sure you include the ELSE `value` because if you don’t, the other rows not mentioned here will be updated to have empty values.

Update multiple records in a table by `ID` =============================================

Below is an example of updating more than one rows in a table by the id field. The field_name field is updated to the corresponding value: value_1, value_2, or value_3 if the id equals to 1, 2, or 3:

UPDATE `table_name` SET `field_name` = CASE `id`
WHEN '1' THEN 'value_1'
WHEN '2' THEN 'value_2'
WHEN '3' THEN 'value_3'
ELSE `field_name`
END

Yet another way =============================================

There’s also another way of doing this: http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html

Or: INSERT … ON DUPLICATE KEY UPDATE

Subscribe to Kavoir: blog feed

You should also read:

curiousity is wasting my time November 11, 2009 at 2:21 pm

If all the record that want to be updated is the same, just

UPDATE myTable SET type=”A”, model=”B” WHERE id IN ( 1, 2, 3)

Yang Yang February 26, 2010 at 5:53 pm

Nice!

charles caadlawon February 16, 2010 at 1:38 pm

hello i want to ask if how can i able to update one single row in different tables. i use php and mysql as my repository

Marty Jongepier March 27, 2010 at 6:18 am

Excellent! Took me long to find this, have been pulling my hair out for days until I found this! Thank you!

AgDude April 16, 2010 at 9:29 pm

Excellent Tip! It seems really simple now that I see it, but I have been using a lot of slow loops for a long time. Thanks.

cheers!!!!! May 17, 2010 at 4:43 pm

we have been fighting the slow sql update problem for days now,

your solution was quick to implement and works like a charm.

thanks!!!!

Colin Bodkin May 24, 2010 at 11:11 pm

I am using MySQL 5.0. Do the databases need to be of a certain type (MyISAM, InnoDB) for this to work?

Yang Yang May 25, 2010 at 12:07 pm

I think either type is fine with this approach.

vaibhav July 29, 2011 at 6:49 pm

no one is better user oracle 10i

Gary November 9, 2010 at 2:27 pm

Sorry I’m a bit lost here – is your update method using CASE part of the original method (eg: creating a temporary table, inserting first) or is that a way to avoid a temporary table all together? Could you please provide the structure of the options table? Thanks a lot, I would appreciate it.

JavaGenious December 8, 2010 at 11:51 am

Thanks for the info.

Can we write a single MySQL query to update multiple ID’s inside the same table?

Yang Yang March 17, 2011 at 9:34 am

The SQL is added at the end of the article.

Rusty January 28, 2011 at 7:34 pm

Have you benchmarked the CASE statement to see if it is actually better than the temporary table solution?

I’d also be interested in how both solutions compare to just doing an INSERT ON DUPLICATE KEY UPDATE.

Mark September 27, 2011 at 1:28 am

As an alternative to an ELSE value, you can provide a WHERE clause:

UPDATE `table` SET `col` = CASE `id`
WHEN 1 THEN ‘one’
WHEN 2 THEN ‘two’
WHEN 3 THEN ‘three’
END
WHERE `id` IN (1, 2, 3)

Same result but without doing a no-op update no rows you don’t care about.

Col SC Sood (Retd) September 28, 2011 at 10:57 pm

I used
UPDATE `table_name` SET `field_name` = CASE `id`
WHEN ’1′ THEN ‘value_1′
WHEN ’2′ THEN ‘value_2′
WHEN ’3′ THEN ‘value_3′
ELSE `field_name`
END

I am thrilled! Thanks a lot!!

Aaron November 10, 2011 at 1:52 pm

Here’s a much more efficient MySQL-flavored solution:

INSERT INTO tbl_name
(pk_cols, update_cols)
VALUES
(pk_vals, update_vals),

(pk_vals, update_vals)
ON DUPLICATE KEY UPDATE
update_col=VALUES(update_col),

update_col=VALUES(update_col);

OPTIMIZE TABLE tbl_name;

Example as per article:
INSERT INTO categories
(id, display_order, title)
VALUES
(1, 3, ‘New Title 1′),
(2, 4, ‘New Title 2′),
(3, 5, ‘New Title 3′)
ON DUPLICATE KEY UPDATE
display_order=VALUES(display_order),
title=VALUES(title);

OPTIMIZE TABLE tbl_name;

The advantage of this solution is that it’s driven by the indexing of the primary keys, on which a CASE cannot rely. The OPTIMIZE TABLE part is only advantageous when updating variable-length cells (i.e. VARCHAR, VARBINARY, BLOB, or TEXT). For multiple-column primary keys, you simply specify all of them in the statement.

Lalou April 24, 2012 at 6:22 pm

Hello dear friends.
I want to update multiple rows on my table using php, but I have this error:
Notice: Undefined offset: 7 in C:\Program Files\EasyPHP-5.3.9\www\ajane\admin\creacomok.php on line 198

That’is my code :

$designationm = array();
$datelivm = array();
$etatm = array();
$qtem = array();
foreach ($_POST['designationm'] as $desim => $dm) { array_push ($designationm, $dm); }
foreach ($_POST['datelivm'] as $datlm => $dlm) { array_push ($datelivm, $dlm); }
foreach ($_POST['etatm'] as $etm => $etam) { array_push ($etatm, $etam); }
foreach ($_POST['qtem'] as $qm => $qtm) { array_push ($qtem, $qtm); }

include’conecdb.php’;
$sql3=”SELECT * FROM commande”;
$result3=mysql_query($sql3);
$count=mysql_num_rows($result3);

for($i=1;$i<$count;$i++) {
$sql1='UPDATE commande SET designation="'.$designationm[$i].'", dateliv="'.$datelivm[$i].'", etat="'.$etatm[$i].'", qte="'.$qtem[$i].'" WHERE numcli="'.$numcli.'"';
$result1=mysql_query($sql1);
}

Craig August 13, 2012 at 7:34 pm

I tried using:

UPDATE `table` SET `col` = CASE `id`
WHEN 1 THEN ‘one’
WHEN 2 THEN ‘two’
WHEN 3 THEN ‘three’
END
WHERE `id` IN (1, 2, 3)

but performance was very similar to doing individual updates.

jihane January 31, 2013 at 6:47 pm

GOOD IDEA THANKS

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: