Mysql batch update multiple records of the same field as a different value method _mysql

First MySQL updates a field of data, which is generally written like this:

UPDATE mytable SET myfield = ' value ' WHERE Other_field = ' other_value ';

You can also use in to specify the records to update:

UPDATE mytable SET myfield = ' value ' WHERE Other_field in (' other_values ');

attention here . ' Other_values ' is a comma (,) delimited string, such as: 1,2,3

If you update more than one piece of data and each record is updated with a different value, many people might write this:

foreach ($values as $id => $myvalue) {
 $sql = "UPDATE mytable SET myfield = $myvalue WHERE id = $id";
 mysql_query ($sql);

That is, an update record for a loop. A record update once, this performance is very poor, but also easy to cause congestion.

So can a SQL statement to achieve batch update?

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

UPDATE mytable
 SET myfield = case ID if
 1 THEN ' myvalue1 ' when
 2 THEN ' myvalue2 ' when
 3 THEN ' Myvalue3 ' C5/>end
WHERE Other_field (' other_values ')

If the Where condition query out the ID of the record is not within the case range, MyField will be set to null.

If you update multiple values, you need to modify them only slightly:

UPDATE mytable
 SET myfield1 = case ID if
 1 THEN ' Myvalue11 ' when
 2 THEN ' Myvalue12 ' when
 3 THEN ' Myva Lue13 ' End
 ,
 myfield2 = case ID when
 1 THEN ' Myvalue21 ' when
 2 THEN ' Myvalue22 ' when
 3 THEN ' Myval Ue23 '
 end
WHERE ID in (1,2,3)

Here, take PHP for example, construct these two MySQL statements:

1, update a number of individual fields for different values, MySQL mode

$ids _values = Array (
 1 =>,
 2 =>,
 3 =>,
 4 =>,
 5 =>,
 6 =>,
 7 =>,
 8 =>,
);
 
$ids = Implode (', ', Array_keys ($ids _values));
$sql = "UPDATE mytable SET myfield = case ID";
foreach ($ids _values as $id => $myvalue) {
 $sql. = sprintf ("When%d THEN%d", $id, $myvalue);
$sql. = "End WHERE ID in ($ids)";
echo $sql. "; <br/> ";

Output:

UPDATE mytable SET myfield = case ID when 1 THEN then 2 THEN when 3 THEN at 4 THEN when 5 THEN N when 7 THEN 8 THEN, WHERE ID in (1,2,3,4,5,6,7,8);

2, update multiple fields for different values, PDO mode

 $data = Array (array (' ID ' => 1, ' Myfield1val ' =>-one, ' Myfield2val ' =>), array
(' ID ' => 2, ' Myfield1val ' =>, ' myfield2val ' => 222));
$where _in_ids = Implode (', ', Array_map (function ($v) {return ": id_". $v [' id '];}, $data));
$update _sql = ' update mytable SET ';

$params = Array ();
$update _sql. = ' Myfield1 = case ID '; foreach ($data as $key => $item) {$update _sql. = "When:id_". $key. "Then:myfield1val_". $key.
 " ";
 $params [": Id_". $key] = $item [' id '];
$params [": Myfield1val_". $key] = $item [' Myfield1val '];

} $update _sql. = "End";
$update _sql. = ', myfield2 = case ID '; foreach ($data as $key => $item) {$update _sql. = "When:id_". $key. "Then:myfield2val_". $key.
 " ";
 $params [": Id_". $key] = $item [' id '];
$params [": myfield1va2_". $key] = $item [' Myfield2val '];

} $update _sql. = "End"; $update _sql. = "WHERE ID in (". $where _in_ids.
")"; echo $update _sql. ";
<br/> "; 
Var_dump ($params); 

Output:

UPDATE mytable SET myfield1 = case ID when:id_0 then:myfield1val_0 when:id_1 then:myfield1val_1 = case ID When:id_0 then:myfield2val_0 when:id_1 Then:myfield2val_1 end WHERE ID in (: id_1,:id_2);

Array (size=6)
 ': id_0 ' => int 1
 ': myfield1val_0 ' => int
 ': id_1 ' => int 2
 ': Myfield1val_1 ' =&G T int
 ': myfield1va2_0 ' => int
 ': myfield1va2_1 ' => int 222

Three other batch update methods

1. replace into batch update

Replace into mytable (ID, MyField) VALUES (1, ' value1 '), (2, ' value2 '), (3, ' value3 ');

2. Insert INTO ... on duplicate key update bulk update

INSERT INTO MyTable (ID, myfield1, myfield2) VALUES (1, ' value11 ', ' Value21 '), (2, ' value12 ', ' Value22 '), (3, ' value13 ', ' Value23 ') on duplicate key update myfield1=values (MYFIELD2), Values (MYFIELD2) +values (ID);

3. Temporary tables

DROP TABLE IF EXISTS ' tmptable ';
Create temporary table tmptable (ID int (4) primary key,myfield varchar);
INSERT into tmptable values (1, ' value1 '), (2, ' value2 '), (3, ' value3 ');
Update MyTable, tmptable set Mytable.myfield = Tmptable.myfield where mytable.id = tmptable.id;

1. "Replace into" and "insert into" updates all rely on primary keys or unique values and can cause structural pitfalls in the operation of new records
   
2. The "replace into" operation is essentially a delete of a duplicate record and then an insert, and if the updated field is not completely missing, the field will be set to the default value
   
3. Insert INTO is just an update duplicate record, and the changed field can only follow the formula value
   
4. The temporary table mode requires the user to have create permissions for the temporary table
   
5. In a small number of "replace into" and "insert into" performance best, a large number of "temporary table" is the best, "case" has a common type and no structural problems

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.