更新记录的时注意，更新是针对记录(基于主键所代表的记录)，因此任何更新，都必须带有主键字段。
/**
* 批量更新数据
* @param string $table_name
* @param array $data
* @param string $field
* @return bool|false|int
*/function db_batch_update($table_name='',$data=array(),$field=''){
if(!$table_name||!$data||!$field){
return false;
}else{
$sql='UPDATE '.$table_name;
}
$con=array();
$con_sql=array();
$fields=array();
foreach ($data as $key => $value) {
$x=0;
foreach ($value as $k => $v) {
if($k!=$field&&!$con[$x]&&$x==0){
$con[$x]=" set {$k} = (CASE {$field} ";
}elseif($k!=$field&&!$con[$x]&&$x>0){
$con[$x]=" {$k} = (CASE {$field} ";
}
if($k!=$field){
$temp=$value[$field];
$con_sql[$x].= " WHEN '{$temp}' THEN '{$v}' ";
$x++;
}
}
$temp=$value[$field];
if(!in_array($temp,$fields)){
$fields[]=$temp;
}
}
$num=count($con)-1;
foreach ($con as $key => $value) {
foreach ($con_sql as $k => $v) {
if($k==$key&&$key<$num){
$sql.=$value.$v.' end),';
}elseif($k==$key&&$key==$num){
$sql.=$value.$v.' end)';
}
}
}
$str=implode(',',$fields);
$sql.=" where {$field} in({$str})";
$res=M($table_name)->execute($sql);
return $res;}更新数据
array(2) {
[0] => array(15) {
["user"] => string(6) "000243"
["cname"] => string(6) "李杰"
["chief"] => string(9) "代令建"
["big_area"] => string(6) "广东"
["newreg_num"] => int(9)
["neword_num"] => int(1)
["price_pat"] => int(0)
["price_pats"] => int(183)
["regcic_num"] => int(13)
["free_num"] => int(82)
["cicnum_30d"] => int(0)
["has_orders_clinics_num"] => int(82)
["active_clinics_rate"] => string(4) "100%"
["date_sample"] => string(10) "2017-04-21"
["id"] => string(1) "1"
}
[1] => array(15) {
["user"] => string(6) "000244"
["cname"] => string(9) "陈晓东"
["chief"] => string(9) "卢汉良"
["big_area"] => string(6) "广东"
["newreg_num"] => int(10)
["neword_num"] => int(4)
["price_pat"] => int(0)
["price_pats"] => int(3105)
["regcic_num"] => int(15)
["free_num"] => int(12)
["cicnum_30d"] => int(0)
["has_orders_clinics_num"] => int(4)
["active_clinics_rate"] => string(3) "27%"
["date_sample"] => string(10) "2017-04-21"
["id"] => string(1) "2"
}}SQL语句
UPDATE rpt_sells_daily set user = (CASE id WHEN '1' THEN '000243' WHEN '2' THEN '000244' end), cname = (CASE id WHEN '1' THEN '李杰' WHEN '2' THEN '陈晓东' end), chief = (CASE id WHEN '1' THEN '代令建' WHEN '2' THEN '卢汉良' end), big_area = (CASE id WHEN '1' THEN '广东' WHEN '2' THEN '广东' end), newreg_num = (CASE id WHEN '1' THEN '9' WHEN '2' THEN '10' end), neword_num = (CASE id WHEN '1' THEN '1' WHEN '2' THEN '4' end), price_pat = (CASE id WHEN '1' THEN '0' WHEN '2' THEN '0' end), price_pats = (CASE id WHEN '1' THEN '183' WHEN '2' THEN '3105' end), regcic_num = (CASE id WHEN '1' THEN '13' WHEN '2' THEN '15' end), free_num = (CASE id WHEN '1' THEN '82' WHEN '2' THEN '12' end), cicnum_30d = (CASE id WHEN '1' THEN '0' WHEN '2' THEN '0' end), has_orders_clinics_num = (CASE id WHEN '1' THEN '82' WHEN '2' THEN '4' end), active_clinics_rate = (CASE id WHEN '1' THEN '100%' WHEN '2' THEN '27%' end), date_sample = (CASE id WHEN '1' THEN '2017-04-21' WHEN '2' THEN '2017-04-21' end) where id in(1,2)