How do I update multiple rows in one single function call?

Question

I want to update multiple rows in one query, for example as with the following code.

db_query("update bestsellers set count = 4 where product_id = 30; update bestsellers set count = 5 where product_id = 32; update bestsellers set count = 1 where product_id = 34;")->execute();

The server returns a 500 error. Is there any way to achieve this?

Answer 1

While you can probably update multiple rows using one call to db_query(...)->execute() (if you use a single SQL query that will updates multiple rows), that's not what the OP code shows. The code try to execute multiple SQL queries with a single call to db_query(...)->execute(), which is not supported by the API. Also, as stated in its documentation for Drupal 7, db_query() should not be used for update queries. Instead, use db_update().

The following would be the clean and safe way to do it with multiple db_update() queries

$counts = [
  30 => 4,
  32 => 5,
  34 => 1
];
foreach ($counts as $product_id => $count) {
  db_update('bestsellers')
    ->condition('product_id' => $product_id)
    ->fields(['count' => $count])
    ->execute();
}

It may also works with a query using a CASE in in the SET clause

db_query("
    UPDATE bestsellers
    SET count = CASE product_id
        WHEN 30 THEN 4
        WHEN 32 THEN 5
        WHEN 34 THEN 1
    END
    WHERE product_id IN (30,32,34)
")->execute();