update
a
inner
join
b
on
a.bid=b.id
set
a.x=b.x,a.y=b.y ;
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
;
|
MySQL How to update one field value in a table equals a field value for another table