Typically, we use the following SQL statement to update field values:
UPDATE mytable SET myfield= ' value ' WHERE other_field= ' other_value '; 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 a single row of records. If I want to rearrange the order of these categories, for example (Tutorial Guide, window display, free resources), then you need to update the Display_order field of the corresponding row of the Categories table, which involves updating the multi-line record 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:
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 executes 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, while improving the system speed. Fortunately, there is a better solution, except that the SQL statement is slightly more complex, but only one query can be executed, with the following syntax:
UPDATE mytable SET myfield = Case Other_field If 1 Then ' value ' when 2 Then ' value ' is 3 Then ' value ' C6/>endwhere ID in (No.)
To get back to the example of our catalogue, we can use the following SQL statement:
UPDATE categories SET display_order = case ID when 1 then 3 if 2 then 1 when 3 then 2 endwhere ID in (a)
Such SQL statements are very easy to understand, that is, the use of many programming languages have the keyword case, according to the ID field value to make different branches of the type of judgment, and then update the Display_order field value. For example, the original id=1 record of the display_order changed to 3,id=2 Records of the display_order changed to 1, only one query to update the multi-line record. In the usual case, where clause is optional, the meaning of adding the WHERE clause is the same as other ordinary SQL used in the WHERE clause. If you are using a MySQL database, you can read more about the documentation for the case statement: Statement
If you need to update multiple fields of a row of records, you can use the following SQL statement:
UPDATE categories SET display_order = case ID when 1 then 3 if 2 then 4 when 3 then 5 END, title = case ID If 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 scheme greatly reduces the number of query operations of the database, greatly saves the system resources, but how to combine with our programming language? We still use the example of the directory just now, here is a sample PHP program:
$display _order = Array ( 1 = 4, 2 = 1, 3 = 2, 4 = 3, 5 = 9, 6 = 5, 7 =& Gt 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 query was executed, and the time saved by the above example would be negligible compared to the 8 update statements executed in the loop. But think about the benefits you'll find when you need to update 10,0000 or more lines of records! The only thing to note 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 can still execute smoothly in PHP 1,000,960, I queried the PHP document and did not find the maximum length of the specified string.
Article reference from: http://www.jbxue.com/db/mysql_update_h4hxn37M2.html
Execute an SQL query and modify the update multiline record
