update – AgileAdam.com

Here’s a quick tip regarding Drupal database manipulation. Specifically, I needed a way to flip a boolean value (from 1 to 0) on the first N rows in a database table that matched a specific set of conditions.

The Problem

It seems you cannot enforce a LIMIT on an UPDATE query using a static query in Drupal.

Here’s what I was trying to do:

$db = \Drupal::database();
$query = $db->query('UPDATE {mytable} SET ondemand=:zero WHERE ondemand=:one ORDER BY imported_at ASC LIMIT :limit', [
  ':zero' => 0,
  ':one' => 1,
  ':limit' => $qtyToConvert,
]);
$query->execute();

$db = \Drupal::database();

$query = $db->query('UPDATE {mytable} SET ondemand=:zero WHERE ondemand=:one ORDER BY imported_at ASC LIMIT :limit', [

':zero' => 0,

':one' => 1,

':limit' => $qtyToConvert,

]);

$query->execute();

This throws an error. I believe the issue is caused by the variable substitution wrapping the limit value in quotes. The error message starts with:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1...

1	SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1...

If you run this query manually in MySQL, it works fine with LIMIT 10 but doesn’t work with LIMIT '10' (it throws the same error shown above).

The Solution

Given dynamic queries are favored over static queries, and that this static query doesn’t work, I ended up getting to the finish line with two dynamic queries.

I have tested the solution on 10,000 records and it took < 6 seconds to process the entire request on my local dev lando site.

“code” is a unique identifier column in the database table.

$db = \Drupal::database();
$query = $db->select('mytable', 'mt')
  ->condition('ondemand', 1)
  ->orderBy('imported_at')
  ->fields('mt', ['code'])
  ->range(0, $qtyToConvert);
$codesToReset = $query->execute()->fetchCol();

$db->update('mytable')
  ->fields(['ondemand' => 0])
  ->condition('code', $codesToReset, 'IN')
  ->execute();
return count($codesToReset);

$db = \Drupal::database();

$query = $db->select('mytable', 'mt')

->condition('ondemand', 1)

->orderBy('imported_at')

->fields('mt', ['code'])

->range(0, $qtyToConvert);

$codesToReset = $query->execute()->fetchCol();

$db->update('mytable')

->fields(['ondemand' => 0])

->condition('code', $codesToReset, 'IN')

->execute();

return count($codesToReset);