Batch modification for data in MySQL

Today encountered a batch update of data, check the official documents, fortunately found. English stuff, everybody be patient ha

Http://dev.mysql.com/doc/refman/5.7/en/update.html

In general, when using data editing:

UPDATE tablename SET field_1= ' value ', field_2= ' value ' [...] WHERE condition_field= ' value '

Or

UPDATE tablename SET field_1= ' value ', [field_2 ...] where Condition_field in (' Values ')

If you update multiple data for a different value, how do you do it?

Probably most people would write this:

foreach ($conditions as $field + $value) {$sql = "UPDATE tablename SET field = $value WHERE id = $id"; mysql_query ($sql);}

This is done by updating a single piece of data, which is less efficient.

Is there a SQL to work with? In fact, MySQL does not provide a direct way to operate, you can look at the official documents, but the English look carefully OH

(Official document: http://dev.mysql.com/doc/refman/5.7/en/update.html)

UPDATE tablename SET field = case ID where 1 Then ' value ' where 2 Then ' value ' Endwhere ID in (all

Update multiple fields?

UPDATE tablename SET field_1 = case ID where 1 Then ' value ' where 2 Then ' value ' END, field_2 = CA SE ID WHERE 1 Then ' value ', where 2 Then ' value ' Endwhere ID in (All-in-a-

The idea is roughly like this, if it is PHP then you can refer to the method of writing:

/** *  batch update data, must be a two-bit array  * *  @param  array  $data   need to follow new data  *  such as: $ data[' field name ' [' PRIMARY key ID ']=> ' value '  * *  @return  boolean */public function  UpdateAll ($data,  $dbname)  {if  (Empty ($data))  {return false;} $sql  =  "update ". $dbname. "  SET  "; $total  = count ($data); $i  = 1; $idsArr  = array ();foreach  ($ data as  $field  =>  $val)  {$sql  .=    $field  = case id   ";foreach  ($val  as  $id  =>  $v)  {$sql  .= sprintf ("  when  %d THEN  '%s '   ',  $id,  $v);if  (!in_array ($id,  $idsArr))  {$IDSARR []  =  $id;}} if  ($i  ==  $total)  {$sql  .= " END ";}  else {$sql  .= " END, ";} $i + +;} $ids  = implode (', ',  $idsArr); $sql  .=  " where id in  ($ids) ";//todo  The specific method of execution wrote (￣▽￣)"  } 

This article is from the "Chase Dream" blog, please be sure to keep this source http://dreameng.blog.51cto.com/1187899/1746917

Batch modification for data in MySQL