MySQL statement: Batch update of multiple records of different

MySQL UPDATE statement is simple, update a field of the data, generally write:

UPDATE SET = ' value ' WHERE = ' Other_value ';

If you update the same field with the same value, MySQL is also simple, modify where you can:

UPDATE SET = ' value ' WHERE inch ('other_values');

Note here that ' other_values ' is a comma (,) delimited string, such as:

If you update more than one data for a different value, many people might write this:

 as = $ordinal) {    = "UPDATESET=WHERE=  $id"    mysql_query ( $sql);}

That is, an update record that loops through one article. One record update once, so performance is poor, it is also easy to cause blocking.

So can a SQL statement implement batch update? MySQL does not provide a straightforward way to implement batch updates, but it can be accomplished with a little bit of skill.

UPDATEmytableSETMyField=  CaseID when 1  Then 'value'         when 2  Then 'value'         when 3  Then 'value'    ENDWHEREIdinch(1,2,3)

This is a small trick to implement a batch update. As an example:

UPDATECategoriesSETDisplay_order=  CaseID when 1  Then 3         when 2  Then 4         when 3  Then 5    ENDWHEREIdinch(1,2,3)

This SQL means, update the Display_order field, if id=1 the value of Display_order is 3, if id=2 Display_order value is 4, if id=3 then Display_order value is 5. That is, the conditional statements are written together. The where section here does not affect the execution of the code, but it improves the efficiency of SQL execution. Make sure that the SQL statement executes only the number of rows that need to be modified, where only 3 of the data is updated, while the WHERE clause ensures that only 3 rows of data are executed. If you update multiple values, you only need to modify them slightly:

UPDATECategoriesSETDisplay_order=  CaseID when 1  Then 3         when 2  Then 4         when 3  Then 5    END, title=  CaseID when 1  Then 'New Title 1'         when 2  Then 'New Title 2'         when 3  Then 'New Title 3'    ENDWHEREIdinch(1,2,3)

Here, a MySQL statement has been completed to update multiple records. But to use in the business, you need to combine the service-side language, here in PHP, for example, constructs this MySQL statement:

$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="UPDATECategoriesSETDisplay_order=  Caseid "; foreach ($display _order as$id=$ordinal) {$sql.=sprintf (" when %D Then %d ", $id, $ordinal);} $sql.="END WHEREIdinch($ids) "; Echo $sql;

In this example, there are 8 records to update. The code is easy to understand, have you learned it?

MySQL statement: Batch update of multiple records of different