1

This works fine:

SELECT * FROM glinks_BuildRelations WHERE cat_id = 284 LIMIT 10,100

Yet when tring to do an update, I get:

UPDATE glinks_BuildRelations SET page_num = 1 WHERE cat_id = 284 LIMIT 10,100

The error is no help:

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 '100' at line 1

What gives?

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
Andrew Newby
  • 4,941
  • 6
  • 40
  • 81
  • 1
    Does this answer your question? [LIMIT offset or OFFSET in an UPDATE SQL query](https://stackoverflow.com/questions/10728775/limit-offset-or-offset-in-an-update-sql-query) – Sebastian Brosch Dec 04 '19 at 09:10
  • @CyrilleConMorales thanks - still the same error. I even tried `LIMIT 100 OFFSET 10` – Andrew Newby Dec 04 '19 at 09:11
  • 1
    possible duplicate of [this](https://stackoverflow.com/questions/6289729/mysql-update-query-with-limit) – jagad89 Dec 04 '19 at 09:12

1 Answers1

4

LIMIT cannot be directly used this way with a MySQL update statement. We can get around this error by using a subquery to find the matching records you want to update:

UPDATE glinks_BuildRelations
SET page_num = 1
WHERE id IN (SELECT id FROM (
                 SELECT id FROM glinks_BuildRelations WHERE cat_id = 284
                 ORDER BY some_col LIMIT 10, 100)x );

I assume here that id is a primary key column in your table. Note that using LIMIT without ORDER BY is fairly meaningless, because you aren't telling MySQL which records you want to retain.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360