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.
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 'firstname.lastname@example.org' 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
You should also read:
- MySQL: Insert if doesn’t exist otherwise update the existing row
- MySQL: Add Statistics Column for the Number Count of Records from Another Table
- MySQL: Find Duplicate Entry / Rows / Records
- MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table
- MySQL: Replace Substring with Another String – the MySQL String Replace Function