MySQL statement: Batch update of different values for multiple records
MySQL UPDATE statement is simple, update a field of the data, generally write:
1 |
UPDATE mytable SET myfield = ‘value‘ WHERE other_field = ‘other_value‘ ; |
If you update the same field with the same value,MySQL is also simple, modify where you can:
1 |
UPDATE mytable SET myfield = ‘value‘ WHERE other_field in ( ‘other_values‘ ); |
Note here that ' other_values ' is a comma (,) delimited string, such as:
If you update more than one data for a different value, many people might write this:
1234 |
foreach ( $display _ Order as $id => $ordinal ) {      $sql = "UPDATE categories SET Display_order = $ordinal WHERE id = $id"      mysql_query ( $sql } |
That is, an update record that loops through one article. One record update once, so performance is poor, it is also easy to cause blocking.
So can a SQL statement implement batch update? MySQL does not provide a straightforward way to implement batch updates, but it can be accomplished with a little bit of skill.
1234567 |
UPDATE
mytable
SET
myfield =
CASE
id
WHEN
1
THEN
‘value‘
WHEN
2
THEN
‘value‘
WHEN
3
THEN
‘value‘
END
WHERE
id
IN (1,2,3)
|
This is a small trick to implement a batch update.
As an example:
1234567 |
UPDATE
categories
SET
display_order =
CASE
id
WHEN
1
THEN
3
WHEN
2
THEN
4
WHEN
3
THEN
5
END
WHERE
id
IN (1,2,3)
|
This SQL means, update the Display_order field, if id=1 the value of Display_order is 3, if id=2 Display_order value is 4, if id=3 then Display_order value is 5.
That is, the conditional statements are written together.
The where section here does not affect the execution of the code, but it improves the efficiency of SQL execution. Make sure that the SQL statement executes only the number of rows that need to be modified, where only 3 of the data is updated, while the WHERE clause ensures that only 3 rows of data are executed.
If you update multiple values, you only need to modify them slightly:
010203040506070809101112 |
UPDATE
categories
SET
display_order =
CASE
id
WHEN 1
THEN
3
WHEN
2
THEN
4
WHEN
3
THEN
5
END
,
title =
CASE
id
WHEN
1
THEN
‘New Title 1‘
WHEN
2
THEN
‘New Title 2‘
WHEN
3
THEN
‘New Title 3‘
END
WHERE
id
IN
(1,2,3)
|
Here, a MySQL statement has been completed to update multiple records.
But to use in the business, you need to combine the service-side language, here in PHP, for example, constructs this MySQL statement:
0102030405060708091011121314151617 |
$display_order =
array
(
1 => 4,
2 => 1,
3 => 2,
4 => 3,
5 => 9,
6 => 5,
7 => 8,
8 => 9
);
$ids
= implode(
‘,‘
,
array_keys
(
$display_order
));
$sql
=
"UPDATE categories SET display_order = CASE id "
;
foreach (
$display_order
as
$id
=>
$ordinal
) {
$sql
.= sprintf(
"WHEN %d THEN %d "
,
$id
,
$ordinal
);
}
$sql
.=
"END WHERE id IN ($ids)"
;
echo
$sql
;
|
In this example, there are 8 records to update. The code is easy to understand, have you learned it?
MySQL statement: Batch update different values for multiple records [go]