PHP implements splicing SQL statements to batch update multiple fields

This article mainly shares with you the PHP implementation of splicing SQL statements to batch update multiple fields. I hope it can help you.

1. SQL statement

1. Update a field in batches:

UPDATE `user`
SET `name` = CASE id
WHEN 1 THEN '张三'
WHEN 2 THEN '李四'
WHEN 3 THEN '王五'
END
WHERE id IN (1,2,3)

this The meaning of the SQL statement is to update the name field of the user table. If id=1, the value of name is Zhang San. If id=2, the value of name is Li Si.

2. Update multiple fields in batches:

UPDATE `user`
SET
        `name` = CASE id
      WHEN 1 THEN '张三'
  WHEN 2 THEN '李思'
        WHEN 3 THEN '王五'
END,
age = CASE id
WHEN 1 THEN 16
WHEN 2 THEN 17
WHEN 3 THEN 18
END
WHERE id IN (1,2 ,3)

2. Case
/

/@todo 去除表中电话和传真的 '-'
//表的最小ID是1，最大ID是121329;fax和phone都是varchar类型
//每次查询100条数据，拼接sql语句批量更新
public function updeAction(){
    set_time_limit(0);
    $mGather = new ClientGatherModel();
    //$mGather->setDb('gather');    //读线上gather库[区分线上和线下数据库]
    $cnt = 0;
    $minId = 1;
    $maxId = 121329;
    //每次最多插入100条数据，程序拼接
    for ($x=$minId; $x<=$maxId; $x+=100){
        $littleMax = $x+99;
        $querySql = "select * from crm_client_gather where id>=$x and id <=$littleMax order by id asc";
        $result = $mGather->query($querySql);
        $data = [];
        $sql = "";
        if($result){
            foreach ($result as $k => $v){
                $fax = $v[&#39;fax&#39;] ? $v[&#39;fax&#39;] : &#39;&#39;;
                $phone = $v[&#39;phone&#39;] ? $v[&#39;phone&#39;] : &#39;&#39;;
                $id = $v[&#39;id&#39;];
                    
                if($fax || $phone ){
                    if($fax && strstr($fax, &#39;-&#39;)){
                        $fax = substr($fax,strpos($fax,&#39;-&#39;)+1);    //截取第一个 - 后面的内容
                    }
                    if($phone && strstr($phone, &#39;-&#39;)){
                        $phone = substr($phone,strpos($phone,&#39;-&#39;)+1);    //截取第一个 - 后面的内容
                    }
                }
                    
                $data[] = [&#39;id&#39;=>$id,&#39;fax&#39;=>$fax,&#39;phone&#39;=>$phone];
            }
            if($data){
                $sql .= " UPDATE crm_client_gather SET fax = CASE id ";
                $faxArr = array_column($data, &#39;fax&#39;, &#39;id&#39;);
                foreach ($faxArr as $k => $v){
                    $sql .= " WHEN {$k} THEN &#39;{$v}&#39; ";
                }
                $sql .= " END, ";
                    
                $phoneArr = array_column($data, &#39;phone&#39;,&#39;id&#39;);
                $sql .= " phone = CASE id ";
                foreach ( $phoneArr as $k => $v ){
                    $sql .= " WHEN {$k} THEN &#39;{$v}&#39; ";
                }
                $sql .= &#39; END &#39;;
                    
                $idArr = array_column($data, &#39;id&#39;);
                $sql .= " WHERE id IN ( ";
                foreach ($idArr as $k => $v){
                    if($idArr[count($idArr)-1] == $v ){
                        $sql .= $v;
                    }else{
                        $sql .= $v.&#39;,&#39;;
                    }
                }
                $sql .=")";
                    
                $mGather->query($sql);
                    
                if( $littleMax > $maxId ){
                    $biggerId = $maxId;
                }else{
                    $biggerId = $littleMax;
                }
                echo "ID:[{$x}]-[$biggerId] 更新了 [".count($data)."]条数据 ";//打印进度
            }
        }
    }
}

Related recommendations:

Precompilation and understanding of mysql statements in php

Mysql Sql statement annotation collection example sharing

Detailed introduction to multiple sql statements

The above is the detailed content of PHP implements splicing SQL statements to batch update multiple fields. For more information, please follow other related articles on the PHP Chinese website!