如何批量更新資料

如何批量更新資料
/**

• 批量更新

• @param $tableName

• @param array $multipleData

• @return bool

• /
  public function updateBatch($tableName,$multipleData = [])
  {
  try {
  if (empty($multipleData)) {
  throw new \Exception(“資料不能為空”);
  }
  $firstRow = current($multipleData);

  $updateColumn = array_keys($firstRow);
  // 預設以id為條件更新，如果沒有ID則以第一個欄位為條件
  $referenceColumn = isset($firstRow[‘id’]) ? ‘id’ : current($updateColumn);
  unset($updateColumn[0]);
  // 拼接sql語句
  $updateSql = “UPDATE “ . $tableName . “ SET “;
  $sets = [];
  $bindings = [];
  foreach ($updateColumn as $uColumn) {
  $setSql = “" . $uColumn . " = CASE “;
  foreach ($multipleData as $data) {
  $setSql .= “WHEN " . $referenceColumn . " = ? THEN ? “;
  $bindings[] = $data[$referenceColumn];
  $bindings[] = $data[$uColumn];
  }
  $setSql .= “ELSE " . $uColumn . " END “;
  $sets[] = $setSql;
  }
  $updateSql .= implode(‘, ‘, $sets);
  $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
  $bindings = array_merge($bindings, $whereIn);
  $whereIn = rtrim(str_repeat(‘?,’, count($whereIn)), ‘,’);
  $updateSql = rtrim($updateSql, “, “) . “ WHERE " . $referenceColumn . " IN (“ . $whereIn . “)”;
  // 傳入預處理sql語句和對應繫結資料
  return DB::update($updateSql, $bindings);
  } catch (\Exception $e) {
  return false;
  }
  }

本作品採用《CC 協議》，轉載必須註明作者和本文連結