Execute an SQL query and UPDATE multiple rows of records

When you execute an SQL query and UPDATE multiple rows of records, we usually use the following SQL statement to UPDATE the field value: 1 UPDATE mytable SET myfield = 'value' WHERE other_field = 'other _ value'; however, if you want to UPDATE multiple rows of data and each field value in each row record is different, what do you do? 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: 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, except that SQL statements are slightly more complex, but you only need to execute one query. The syntax is as follows: UPDATE mytable SET myfield = CASE other_field WHEN 1 THEN 'value' WHEN 2 THEN 'value' WHEN 3 THEN 'value' ENDWHERE id IN (1, 2, 3) back to the example of the classification directory we just mentioned, we can use the following SQL statement: UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 1 WHEN 3 THEN 2 ENDWHERE id IN (, 3) is easy to understand, in other words, the CASE keyword is used in many programming languages. The id field value is used to determine the current type of different branches, and then the display_order field value is updated. For example, if the display_order of the original record with id = 1 is changed to 3, And the display_order of the record with id = 2 is changed to 1, you only need to execute one query to update the records of multiple rows. In general, the WHERE clause is dispensable. The meaning of adding the WHERE clause is the same as that of other common SQL statements that use the WHERE clause.
To UPDATE multiple fields in a record, use the following SQL statement: 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 'endwhere 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: $ 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 SQL statements} $ SQL. = "end where id IN ($ ids)"; echo $ SQL; mysql_query ($ SQL ); Eight new rows of data are added, but only one database query is executed. Compared with the eight UPDATE statements executed cyclically, the time saved in the preceding 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.