一、SQL语句
1、批量更新一个字段:
UPDATE `user`
SET `name` = CASE id
WHEN 1 THEN '张三'
WHEN 2 THEN '李四'
WHEN 3 THEN '王五'
END
WHERE id IN (1,2,3)
这句sql 的意思是,更新user表的name字段,如果id=1 则name的值为张三,如果id=2 则name的值为李四
2、批量更新多个字段:
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)
二、案例
//@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['fax'] ? $v['fax'] : '';
$phone = $v['phone'] ? $v['phone'] : '';
$id = $v['id'];
if($fax || $phone ){
if($fax && strstr($fax, '-')){
$fax = substr($fax,strpos($fax,'-')+1); //截取第一个 - 后面的内容
}
if($phone && strstr($phone, '-')){
$phone = substr($phone,strpos($phone,'-')+1); //截取第一个 - 后面的内容
}
}
$data[] = ['id'=>$id,'fax'=>$fax,'phone'=>$phone];
}
if($data){
$sql .= " UPDATE crm_client_gather SET fax = CASE id ";
$faxArr = array_column($data, 'fax', 'id');
foreach ($faxArr as $k => $v){
$sql .= " WHEN {$k} THEN '{$v}' ";
}
$sql .= " END, ";
$phoneArr = array_column($data, 'phone','id');
$sql .= " phone = CASE id ";
foreach ( $phoneArr as $k => $v ){
$sql .= " WHEN {$k} THEN '{$v}' ";
}
$sql .= ' END ';
$idArr = array_column($data, 'id');
$sql .= " WHERE id IN ( ";
foreach ($idArr as $k => $v){
if($idArr[count($idArr)-1] == $v ){
$sql .= $v;
}else{
$sql .= $v.',';
}
}
$sql .=")";
$mGather->query($sql);
if( $littleMax > $maxId ){
$biggerId = $maxId;
}else{
$biggerId = $littleMax;
}
echo "ID:[{$x}]-[$biggerId] 更新了 [".count($data)."]条数据 ";//打印进度
}
}
}
}