Update multiple rows with one query

Question

How can I update hundreds of rows at once?

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

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

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

---

Right now I have something like this:

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

Answer 1

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) {
    ...
}