offset and limit syntax error

Question

What is wrong here

phpMyAdmin

update posts set pos = 'right' where pos = 'below' limit 4 offset 10;

I also tried:

update posts set pos = 'right' where pos = 'below' offset 10 limit 4;
update posts set pos = 'right' limit 4 offset 10;
update posts set pos = 'right' offset 10 limit 4;

Always the same error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset 10' at line 1

Read the syntax here: https://dev.mysql.com/doc/refman/5.7/en/update.html — Barmar, Mar 30 '18 at 13:42
When you get a syntax error, why isn't the first thing you do to read the documentation of the syntax? — Barmar, Mar 30 '18 at 13:43
Besides using LIMIT without ORDER BY is also meaningless because MySQL is free to return/update the records in anny order without ORDER BY — Raymond Nijland, Mar 30 '18 at 13:51

Andomar · Answer 1 · 2018-03-30T13:57:53.677

Assuming your posts table has an id primary key, you could:

update  posts 
set     pos = 'right' 
where   id in
        (
        select  id
        from    (
                select  id
                from    posts
                where   pos = 'below' 
                order by
                        id
                limit   4
                offset  10
                ) sub
        )

Example at DB Fiddle. The double subquery is required to work around MySQL's doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' error. Like Raymond Nijland commented, MySQL usually returns rows in the primary key order, but you can't strictly rely on that without an order by.

"MySQL usually returns rows in the primary key order" Yes it's known that InnoDB returns in primary key order. MyISAM follows the physical order within the table data. — Raymond Nijland, Mar 30 '18 at 14:03

Raymond Nijland · Accepted Answer · 2018-03-30T14:14:01.890

2

Like Barmar said in the comments you can't make use of a OFFSET with LIMIT in UPDATE queries.

The best method to replace

update posts set pos = 'right' where pos = 'below' limit 4 offset 10;

Assuming you have a id column with PRIMARY KEY and auto_increment option.. this should work like using limit 4 offset 10 or limit 10. 4

p.s Please note that the id's must be incremental from 11 until 14 without anny deleted id('s).

update posts set pos = 'right' where pos = 'below' and (id > 10 and id <= 14)

p.s this query will work when there are deleted id('s)

update posts set pos = 'right' where pos = 'below' and id > 10 LIMIT 4

edited Mar 30 '18 at 14:14

answered Mar 30 '18 at 13:54

Raymond Nijland

11,488
2
22
34

This relies on rows having incremental `id` values, and will break if rows are deleted from the table – Andomar Mar 30 '18 at 14:05
indeed @Andomar edited mine answer so deletes within that range won't have a influence on the updated record limit – Raymond Nijland Mar 30 '18 at 14:10
@bonaca i've updated this answer because off Andomar's comment.. Use the latest query because that one is more stabile when you use hard deletes on your table. – Raymond Nijland Mar 30 '18 at 14:20
You can't rely on `id` being equal to the row's number. In your updated query, if one of the first 10 rows was deleted, the update wouldn't start at row 10 – Andomar Mar 30 '18 at 15:08

score 1 · Answer 3 · answered Mar 30 '18 at 13:50

LIMIT can be used with UPDATE but with the row count only.

So here below query will work fine without limit

update posts set pos = 'right' where pos = 'below'

Please check below links there are some good points for the same.

MySQL - UPDATE query with LIMIT

As suggest by @Barmar, please check syntex and search little on this before asking here. :)

offset and limit syntax error

3 Answers3