PHP SQL更新查询异常：无法按城市名更新指定state_id的行

错误原因 #

你的UPDATE语句仅用state_id = 9作为过滤条件，这会匹配所有state_id为9的行。每次循环执行更新时，都会把这些行的meta_desc和meta_key覆盖为当前循环的城市值。当循环到最后一个城市时，所有符合条件的行就都被替换成最后一个城市的内容了。

修正方案 #

方案一：循环中针对单条记录更新 #

在UPDATE的WHERE条件中加入当前城市的唯一标识符（比如主键city_id），确保每次只更新循环到的那一行。同时提前预编译更新语句，提升性能：

$selectquery = "SELECT * FROM `cities` WHERE `state_id` = 9";
$stmt = $conn->prepare($selectquery);
$stmt->execute();

// 提前预编译更新语句，避免重复编译
$updateStmt = $pdo->prepare('UPDATE cities SET meta_desc = :desc, meta_key = :key WHERE state_id = 9 AND city_id = :city_id');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $city_name = !empty($row['city_name']) ? $row['city_name'] : '';
    $city_id = $row['city_id']; // 假设表中存在主键city_id

    $new_key = "{$city_name} Businesses, {$city_name} Business, {$city_name} Tourism, .....";
    $new_desc = "Find the best {$city_name} Businesses. Find the best {$city_name} Restaurants, ....";

    $updateStmt->execute([
        ':desc' => $new_desc,
        ':key' => $new_key,
        ':city_id' => $city_id
    ]);
}

方案二：单条SQL批量更新（更高效） #

不需要PHP循环，直接用UPDATE结合子查询完成批量更新，减少数据库交互次数，性能更优：

$sql = "UPDATE cities c
JOIN (
    SELECT city_id, city_name 
    FROM cities 
    WHERE state_id = 9
) sub ON c.city_id = sub.city_id
SET 
    c.meta_desc = CONCAT('Find the best ', sub.city_name, ' Businesses. Find the best ', sub.city_name, ' Restaurants, ....'),
    c.meta_key = CONCAT(sub.city_name, ' Businesses, ', sub.city_name, ' Business, ', sub.city_name, ' Tourism, .....')
WHERE c.state_id = 9";

$stmt = $pdo->prepare($sql);
$stmt->execute();

内容的提问来源于stack exchange，提问作者Skipper