MySQL: Is there a way to update an entire table in one query?

Question

Hey Everyone,

I need to update all the records of a table(shouldn't be over a 100 records). Each record will need to have a field updated with a different value.

Am I going to have to make a query for each update or is there another way? I can't seem to figure out to do it at once

Thanks for the help!

EDIT: To clarify, each field that i'm updating will contain a unique value that is determined on the client-side. So no other tables are involved.

Answer 1

Without knowing anything about the value you will assign to each row, the answer is "probably no". Another poster mentioned that you can affect all rows at once by leaving off the WHERE clause, but that doesn't help you if each row needs it's own value.

If you can provide some info about how you know what value to give each row, there may be a better answer. Awaiting more info...

Answer 2

if the value of the update differs from record to record you will need to create a new UPDATE statement every time.

Answer 3

If the new value of each row can be deduced from the current value of the row, you can do it in a single UPDATE query. Otherwise, no.

One way is to store the often-changed columns in a separate table, DELETE or TRUNCATE that table, and INSERT all the new values in one query (by using a multiple-row insert query).

In MySQL, you have access to ON DUPLICATE KEY UPDATE to improve the first method.

Answer 4

If each value is derived from other values in the row and can be computed in a simple expression, you can do something like this:

UPDATE table_name SET col1 = (col2 / col3);

But if your new values are computed outside of the database (eg, by a script), you probably can't do better than separate update queries for each row.

Answer 5

If each field is going to need a different value, no.

You can have the values in an array, and can write a loop that does it, so you don't need to write each UPDATE by hand.

$myIds = array('1', '2', '3');
$myField = array('something', 'something2', 'something3');

for($i=0; $i<count($myIds); $i++){
   mysql_query("UPDATE table SET field = '{$myField[$i]}' WHERE id = '{$myIds[$i]}');
}

You could even create the $myIds and $myField arrays through SELECT statements, depending on your needs, which would make it fairly painless.

If it's user input, make sure to sanitize those variables though.

Answer 6

The answer depends a lot on the source of the new values.

If you have a short list of new values, you may be able to use CASE:

UPDATE Table1
SET column1 = CASE column1
              WHEN 123 THEN ?
              WHEN 456 THEN ?
              ELSE ?
              END;

This isn't practical if the list of new values is very long.

If you need to update your rows with values that exist in correlated rows of another table, you can use MySQL's multi-table UPDATE syntax:

UPDATE Table1 JOIN Table2 ON (Table1.pk = Table2.fk)
SET Table1.column1 = Table2.column2;

You can also do something similar with a correlated subquery.

UPDATE Table1 SET column1 = (SELECT column2 FROM Table2 WHERE Table2.fk = Table1.pk);

A long time ago when I used MySQL 3.23 (before it supported multi-table update or subqueries), I would run a SELECT whose output was fully-formed UPDATE statements. It was tricky to get all the quotes right, but the result was an SQL script of hundreds of individual UPDATE statements, each changing values in one row. Not very efficient, but if it only needs to be done infrequently, it was fine.

SELECT CONCAT(
  'UPDATE Table1 SET column1 = ', QUOTE(Table2.column2), 
  ' WHERE pk = ', Table2.fk, ';') AS SQL_statement
FROM Table2;