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

Question

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

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;