Typically, we use the following SQL statement to update the field values:
Copy Code code as follows:
UPDATE mytable SET myfield= ' value ' WHERE other_field= ' other_value ';
But what would you do if you wanted to update multiple rows of data and the values of each field in each row were different? For example, my blog has three categories (free resources, tutorials, window display), these categories of information stored in the database table categories, and set the display order field Display_order, each category occupies one row. If I want to rearrange the order of these categories, for example (Tutorial guides, window shows, free resources), then you need to update the Display_order field for the corresponding row in the Categories table, which involves updating the multiple-line record. At first you might think of using a loop to execute multiple UPDATE statements, like the following PHP example:
Copy Code code 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 to execute more than one SQL query, in the system optimization, we always want to reduce the number of database queries as much as possible to reduce resource consumption, while increasing the speed of the system.
Fortunately, there are better solutions, and here are two common scenarios where the SQL statement is slightly more complex, but only one query can be executed, the syntax is as follows:
• First: combination of if--then statements
Copy Code code 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 (1,2,3)
To get back to the example of our catalogue, we can use the following SQL statement:
Copy Code code 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 (1,2,3)
The above scheme has greatly reduced the database query operation times, greatly saving the system resources, but how to combine with our programming language? We still use the example of the catalogue just now, the following is the example of PHP program:
Copy Code code 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); Stitching SQL statements
}
$sql. = "End WHERE ID in ($ids)";
Echo $sql;
mysql_query ($sql);
A total of 8 rows of data were updated in this example, but only one database query was executed, and the time saved by the above example was negligible compared to the 8-time UPDATE statement being executed. But think about it, when you need to update 10,0000 or more line records, you will find the benefits! The only thing to be aware of is the length of the SQL statement, the length of the string that is supported by the program's running environment, the data I am currently getting: The SQL statement length reaches 1,000,960 in PHP and still executes smoothly, I queried the PHP document and did not find the maximum length of the string specified.
• The second Insert method
The insert syntax in MySQL has a condition duplicate KEY update, and this syntax is suitable for records that need to be used to determine whether a record exists or not, and an update if the insertion exists.
Based on this situation, the INSERT statement is still used for the update record, but the field is updated when the primary key is repeatedly restricted. As follows:
Copy Code code as follows:
INSERT into T_member (ID, name, email) VALUES
(1, ' Nick ', ' nick@126.com '),
(4, ' Angel ', ' angel@163.com '),
(7, ' Brank ', ' ba198@126.com ')
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!
