Problem:
I'd like to update many rows with each row having a possibly different value, while using only a single SQL query. A query with a single WHERE clause would be inadequate as a result.
Warning:
This how-to can possibly ruin a database if done wrong, so make sure that you first test it using toy data (and make sure it works properly). Use at your own risk!
Please feel free to point out any typos and mistakes in the comments below.
Solution:
This is a summary (for myself) of the solution found at Karl Rixon's page here. For greater detail, please follow the aforementioned link.
Example table:
We'll use the following table for this example:
| ID | S_ID | Name | Country | One_or_zero |
|---|---|---|---|---|
| 1 | 205G0 | Bob | USA | 1 |
| 2 | 205G0 | Cat | UK | 0 |
| 3 | 319E8 | Jane | Australia | 1 |
| 4 | 518A1 | Joe | Iceland | 1 |
| ... | ... | ... | ... | ... |
Example 1 - multiple rows, single column:
In order to make multiple updates, you can use a CASE block in SQL combined with an appropriate WHERE clause to select the appropriate rows and set the different values. For example, in order to update the column `Country` based on column `ID` alone:
UPDATE `membership_list`
SET `Country` = CASE `ID`
WHEN '1' THEN 'Antarctica'
WHEN '3' THEN 'Canada'
END
WHERE `ID` IN (1,3);
Do NOT forget the WHERE clause otherwise all other values will be set to NULL.
Example 2 - multiple rows, multiple columns:
In order to change more than one column, more than one CASE blocks can be used. For example, in order to both the columns `Country` and `One_or_zero` based on `ID` alone:
UPDATE `membership_list`
SET `Country` = CASE `ID`
WHEN '1' THEN 'Antarctica'
WHEN '3' THEN 'Canada'
END,
`One_or_zero` = CASE `ID`
WHEN '1' THEN '1'
WHEN '3' THEN '1'
END
WHERE `ID` IN (1,3);
Example 3 - multiple rows, multiple WHERE conditions:
Now assuming that you only want to alter all rows in `membership_list` that have '205G0' as the `S_ID` and 'Bob' as the `Name`, and we want to set all `Country` to unique values based on `One_or_zero`:
UPDATE `membership_list`
SET `Country` = CASE `One_or_zero`
WHEN '1' THEN 'Switzerland'
WHEN '0' THEN 'Russia'
END
WHERE `One_or_zero` in (0,1)
AND `S_ID`='205G0'
AND `Name`='Bob';
Example 4 - constructing a PHP query for Example 1:
Among other benefits, a single query for multiple updates becomes particularly useful when using server-side scripting such as PHP, as there is a significant gain of speed when compared to looping through many single queries.
Here is an example script to construct the query for the first example above:
$arr_id_country = array(
'1' => 'Antarctica',
'3' => 'Canada'
);
$str_ids = implode(',', array_keys($arr_id_country));
$str_when_then = "";
foreach($arr_id_country as $id => $country) {
$str_when_then .= sprintf(" WHEN '%d' THEN '%s' ",
$id,
$country // note, you'd sanitize this if from user input
);
}
// whitespace + appends included in example for readability
$template = "UPDATE `membership_list` "
. " SET `Country` = CASE `ID` "
. " %s "
. " END "
. " WHERE `ID` IN (%s);"
$query = sprintf($template, $str_when_then, $str_ids);
// do database queries, etc., here ...
Notes:
Once again, do NOT forget to include the WHERE clause that contains all the values found in the CASE block in order to prevent accidentally affecting other rows unintentionally.
