How to execute an SQL statement to update multiple records

Generally, the following SQL statement is used to update the field value:
Copy codeThe Code is as follows:
UPDATE mytable SET myfield = 'value' WHERE other_field = 'other _ value ';

However, what if you want to update multi-row data and record different field values in each row? For example, my blog has three classification directories (free resources, tutorial guide, and window display). The information of these classification directories is stored in the database table categories, the display_order field is set, and each category occupies one record. If I want to re-arrange the order of these classification directories, such as changing to (Tutorial guide, window display, and free resources), I need to update the display_order field of the corresponding row in the categories table, this involves updating multiple rows of records. At the beginning, you may want to execute multiple UPDATE statements cyclically, just like the following php program example:
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 );
}

This method has no errors and the code is easy to understand. However, more than one SQL query is executed in a loop statement. During system optimization, we always want to minimize the number of database queries to reduce resource usage and increase the system speed.
Fortunately, there are better solutions. The following lists two common solutions, except that SQL statements are slightly more complex, but you only need to execute one query. The syntax is as follows:

• First: 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 (1, 2, 3)

Back to the example of the classification directory we just mentioned, we can 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 (1, 2, 3)

The above solution greatly reduces the number of database queries and saves system resources. But how can we combine it with our programming language? We still use the example of the classification directory just now. The following is a php program example:
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); // concatenate an SQL statement
}
$ SQL. = "END WHERE id IN ($ ids )";
Echo $ SQL;
Mysql_query ($ SQL );

In this example, a total of eight rows of data are updated, but only one database query is executed. Compared with the eight UPDATE statements executed cyclically, the time saved in the above example is negligible. But when you need to update 10, or more rows, you will find the benefits! The only problem to be aware of is the length of the SQL statement. You need to consider the length of the string supported by the program running environment. The data I currently obtain: the SQL statement length reaches 1,000,960 and can still be successfully executed in php, I have searched the php document and have not found that the maximum length of the specified string is clearly defined.

• The second INSERT method
In MySql, the INSERT syntax has a condition duplicate key update. This syntax is applicable when you need to determine whether a record exists. If no record exists, INSERT the record that exists and then UPDATE the record.
Based on the above situation, the insert statement is still used for updating records. However, when the primary key is duplicate, the field is updated. As follows:
Copy codeThe Code is 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 special MySQL syntax, not a standard SQL syntax!