MySQL update changes more than one piece of data

Typically, we use the following SQL statement to update the field values:
Copy CodeThe code is as follows:
UPDATE mytable SET myfield= ' value ' WHERE other_field= ' other_value ';

However, suppose you want to update multiple rows of data, and the values of each field record are different, what would you do? For example, my blog has three categorized folders (free resources, tutorial guides, window displays), which are stored in database table categories, and the Display order field Display_order is set, with each category occupying one row. Suppose I want to rearrange the order of these classified folders again, such as Change to (Tutorial Guide, window display, free resources), then you need to update the Categories table corresponding row of the Display_order field, which involves updating the multi-line record of the problem, At first you might think of a way to run multiple update statements using loops, just like the following PHP program Demo sample:
Copy CodeThe code is as follows:
foreach ($display _order as $id = + $ordinal) {
$sql = "UPDATE categories SET Display_order = $ordinal WHERE id = $id";
mysql_query ($sql);
}

There is nothing wrong with this method, and the code is easy to understand, but in the loop statement run more than once SQL query, in the system optimization, we always want to reduce the number of database queries as much as possible to reduce resource consumption, the same time can improve the system speed.
Fortunately, there are better solutions, the following list of two commonly used scenarios are just a little bit more complex SQL statements, but only need to run a query, syntax such as the following:

? The first type: If--then statement combination
Copy CodeThe code is as follows:
UPDATE mytable
SET MyField = Case Other_field
When 1 Then ' value '
When 2 Then ' value '
When 3 Then ' value '
END
WHERE ID in (All-in-a-

back to our sample of the categorized folder, we were able to use the following SQL statement:
Copy CodeThe code is as follows:
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 (All-in-a-

The above scheme greatly reduces the number of query operations of the database, greatly saving the system resources, but how to combine with our programming language? We still use the sample folder just now, here is a sample of PHP program Demo:
Copy CodeThe code is as follows:
$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); Splicing SQL statements
}
$sql. = "END WHERE ID in ($ids)";
Echo $sql;
mysql_query ($sql);

in this example, a total of 8 rows of data were updated, but only one database query was run, and the time saved by the sample was negligible compared to running 8 update statements in the loop. But think about the advantages you'll find when you need to update 10,0000 or a lot of other line records! The only thing to note is the length of the SQL statement, the length of the strings supported by the program's running environment, the data I get now: The SQL statement can still run smoothly in PHP 1,000,960, I queried the PHP document and did not see the maximum length of the specified string.

? A different Insert method
The insert syntax in MySQL has a condition of duplicate KEY update, which is used for records that are updated if there is a need to infer whether the record exists, or if it does not exist.
based on the above scenario, the INSERT statement is still used to update the record, but the field is updated only when the primary key is repeatedly restricted. For example, the following:
Copy CodeThe code is as follows:
INSERT into T_member (ID, name, email) VALUES
(1, ' Nick ', ' [email protected] '),
(4, ' Angel ', ' [email protected] '),
(7, ' Brank ', ' [email protected] ')
On DUPLICATE KEY UPDATE name=values (name), email=values (email);

Note: On DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!

MySQL update changes more than one piece of data