• Development

    Setting a LIMIT on an UPDATE query in Drupal 8 / 9

    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:

    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:

    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.