MySQL limit clause not accepting 2 arguments

Question

I have really searched this and find nothing about this problem.

Very simple query:

/*get rid of 125 rows starting at 100*/
delete from my_table WHERE category=5 order by editdate DESC LIMIT 100, 125;

This is what is returned:

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

I have not used LIMIT in a while, but at the same time I have not had a problem with it. Error is very mysterious, any ideas?

My version of mysql is 5.x

Yes sorry, I forgot that what I said applies to PostgreSQL only. — Ende Neu, Sep 09 '14 at 12:52
I hope that this answer will help you [http://stackoverflow.com/a/7142118/4022918][1] [1]: http://stackoverflow.com/a/7142118/4022918 — Nandhini C, Sep 09 '14 at 12:55

score 1 · Answer 1 · answered Sep 09 '14 at 12:54

1

This is because the MySQL syntax for DELETE, doesn't allow a second parameter for LIMIT like in the SELECT case. Please check the manual.

answered Sep 09 '14 at 12:54

mgm

143
13

score 0 · Answer 2 · answered Sep 09 '14 at 12:56

0

You can't use a LIMIT offset on a DELETE command. Here's a way to do it:

delete from my_table WHERE ID IN (SELECT ID from my_table WHERE category=5 order by editdate DESC LIMIT 100, 150);

Where ID is the primary ID for my_table.

answered Sep 09 '14 at 12:56

Will

2,790
19
22

score 0 · Answer 3 · answered Sep 09 '14 at 12:59

0

delete from my_table WHERE category=5 order by editdate DESC LIMIT 125;

This will work but you can't use limit like 100,125 . because it shows the selection between rows.

answered Sep 09 '14 at 12:59

RSCode

1,573
2
13
21

MySQL limit clause not accepting 2 arguments

3 Answers3