sql 针对不同的查询id，批量更新字段的方法

最新推荐文章于 2024-08-09 15:17:14 发布

xiaoyanyan_93

最新推荐文章于 2024-08-09 15:17:14 发布

阅读量1.5k

点赞数

文章标签： sql 数据库 php laravel

原文链接：http://t.zoukankan.com/sgm4231-p-10070954.html

版权

根据不同的id,更新不同的字段值
需要更新的数据：
$multipleData  =[
 0 => array:2 [
    "id" => 84
    "delivery_agent_id" => 9
  ]
  1 => array:2 [
    "id" => 171
    "delivery_agent_id" => 26
  ]
  2 => array:2 [
    "id" => 758
    "delivery_agent_id" => 65
  ]];

方法：

protected static function updateBatch($tableName = "", $multipleData = array()){
          if( $tableName && !empty($multipleData) ) {
            // column or fields to update
            $updateColumn = array_keys($multipleData[0]);
            $referenceColumn = $updateColumn[0]; //e.g id
            unset($updateColumn[0]);
            $whereIn = "";

            $q = "UPDATE ".$tableName." SET ";
            foreach ( $updateColumn as $uColumn ) {
                $q .=  $uColumn." = CASE ";

                foreach( $multipleData as $data ) {
                    $q .= "WHEN ".$referenceColumn." = ".$data[$referenceColumn]." THEN '".$data[$uColumn]."' ";
                }
                $q .= "ELSE ".$uColumn." END, ";
            }
            foreach( $multipleData as $data ) {
                $whereIn .= "'".$data[$referenceColumn]."', ";
            }
            $q = rtrim($q, ", ")." WHERE ".$referenceColumn." IN (".  rtrim($whereIn, ', ').")";
//            dd($q);
            // Update
            return DB::update(DB::raw($q));
        } else {
            return false;
        }