使用一个查询更新多行 [英] Update multiple rows with one query

问题描述

如何一次更新数百行?

赞: UPDATE table SET a = ? WHERE b = ? AND c = 1

但是很多行. ?参数是数组...

but for many rows. The ? parameters are arrays...

我阅读了此答案，但它使用了CASE，但我没有认为我可以做到...

I read this answer but it uses CASE and I don't think I can do that...

现在我有这样的东西:

foreach($values as $key => $value)
  $res = $pdo->prepare('UPDATE table SET a = ? WHERE b = ? AND c = 1');
  $res->execute(array($value, $key));
}

推荐答案

要在一次查询中执行此操作，您需要使用CASE和简单值可以参数化.

To do it in a single run of a query, you'd need to use a CASE and assemble the parameters programmatically. SQL doesn't support variadic prepared statements, and only simple values can be parameterized.

或者，定义一条语句，一次只获取一行数据，然后循环运行查询.重复执行是将准备好的语句设计用于此类情况的方式.

Alternatively, define a statement to only take data for one row at a time and run the query in a loop. Repeated execution is how prepared statements are designed to be used for cases like this.

try {
    $query = $db->prepare('UPDATE table SET a = ? WHERE b = ? AND c = 1');
    foreach ($as as $i => $a) {
        $query->execute(array($a, $bs[$i]));
    }
} catch (PDOException $e) {
    ...
}

这篇关于使用一个查询更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！