Execute an SQL statement update record implementation ideas for multiple different values

What would you do if you wanted to update multiple rows of data, and the values of each field in each row were different? This article takes an example to explain how to implement the situation as shown in the title, a friend with this need to understand under normal circumstances, we will 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 do you do if you want to update multiple rows of data, and each field value is different for each row of records? For example, my blog has three categories (free resources, tutorial guides, window displays),
The information for these categories is stored in the database table categories, and the Display order field Display_order is set, with each category occupying one row of records. If I want to rearrange the order of these categories,
For example, change to (Tutorial Guide, window display, free resources), then 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 use loops to execute multiple UPDATE statements, like the following example of a PHP program: The copy 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 there are more than one SQL query executed in the loop statement, and we always want to reduce the number of database queries as much as possible while doing system optimization.
To reduce resource usage while increasing system speed. Fortunately, there are better solutions, the following list of two common scenarios is only a little more complex SQL statement, but only one query can be executed once, the syntax is as follows:? The first kind: The If--then statement combined with the copy Code code is as follows: UPDATE mytable SET myfield = Case Other_field If 1 then ' value ' while 2 Then ' value ' when 3 Then ' value ' END WHERE ID in (All-in-a-way) back to the example of our catalog just now, we can use The following SQL statement: The copy Code code is as follows: UPDATE categories SET Display_order = case ID if 1 then 3 while 2 then 4 when 3 then 5 END, title = CA SE 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 database The number of polling operations, greatly saving the system resources, but how to integrate with our programming language? We still use the example of the directory just now, the following is a sample PHP program: Copy code is as follows: $display _order = Array (1 = 4, 2 = 1, 3 = 2, 4 = 3, 5 = 9, 6 =&G T 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 and%d", $id, $ordinal); Splicing SQL statement} $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 was executedQuery, the time saved by the above example is negligible compared to executing 8 update statements in a loop. But think about it,
When you need to update 10,0000 or more line records, you will find the benefits of this! The only thing to note is the length of the SQL statement, which takes into account the length of the string supported by the program's running environment.
I am currently getting data: SQL statement length up to 1,000,960 in PHP can still be executed smoothly, I queried the PHP document did not find the maximum length of the specified string. The second insert method in MySQL has a condition for the insert syntax duplicate The KEY update, this syntax and the appropriate record to be used if the record needs to be determined if the records exist, or if the insertion exists. Based on the above scenario, the INSERT statement is still used for updating the record, but the field is updated when the primary key is limited to repeating. Copy the code as follows: INSERT into T_member (ID, name, email) VALUES (1, ' Nick ', ' [email protected] '), (4, ' Angel ', ' [email protecte D] '), (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!

　　

Execute an SQL statement update record implementation ideas for multiple different values