php 批量更新,批量更新 · ThinkPHP3.2.3 · 看云

更新记录的时注意，更新是针对记录(基于主键所代表的记录)，因此任何更新，都必须带有主键字段。

~~~

/**

* 批量更新数据

* @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

$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)

~~~