Mysql delete order by

Question

I have a table and I only display the latest 30 rows by order by ID.

I'm trying to delete any rows after the 30 newest rows by using this query below.

DELETE FROM table WHERE type = 'test' ORDER BY id DESC LIMIT 30, 60

I keep getting this error below

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

What am I doing wrong?

score 31 · Accepted Answer · answered Sep 12 '12 at 06:25

31

Try this one,

DELETE FROM table
WHERE ID IN
        (
        SELECT ID
        FROM
            (
                SELECT ID
                FROM table
                WHERE Type = 'TEST'
                ORDER BY ID
                LIMIT 30,60
            ) a
        )

answered Sep 12 '12 at 06:25

John Woo

258,903
69
498
492

2

Note the `a` serves as an alias for the subquery: http://stackoverflow.com/a/14767216/1538531 Thank you. This helped me a bunch! – Derek Feb 23 '15 at 17:18
3

The double nesting is required to implicitly create a temporary table as a workaround. See [this answer for an explanation](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/45498#45498). – Nov 24 '16 at 21:52
If you have join with another table check out this - https://stackoverflow.com/a/12202674/4050261 – Adarsh Madrecha Feb 03 '19 at 20:55

alexn · Answer 2 · 2012-09-12T06:42:23.813

8

Second edit: While MySQL supports LIMIT in delete statements, it does not allow an OFFSET. This means that you cannot skip the first 30 rows.

Make a subselect on id (or any other primary key):

DELETE FROM table WHERE id IN (SELECT id FROM table WHERE type = 'test' ORDER BY id DESC LIMIT 30, 60)

edited Sep 12 '12 at 06:42

answered Sep 12 '12 at 06:23

alexn

57,867
14
111
145

2

I'm getting #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Abby E Sep 12 '12 at 06:24
Actually, you might be surprised that it in fact [does](http://dev.mysql.com/doc/refman/5.0/en/delete.html) *DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]* It is however not supported on multiple table delete statements. – Fluffeh Sep 12 '12 at 06:28
Yep, saw that. Editing my question. – alexn Sep 12 '12 at 06:29
2

@alexn Hate to nitpick, but won't that delete the first thirty rows which is exactly what the OP wants to keep? He wants to delete the thirty rows after the ones that would be deleted by your query – Fluffeh Sep 12 '12 at 06:33
@Fluffeh Yep, you are right. Can you provide a solution to using LIMIT with a specified offset and row count? I'm not sure it's supported. – alexn Sep 12 '12 at 06:40
2

@alexn No, it simply doesn't support an offset. The only way is to use the query that John Woo gave (with the double subselect) – Fluffeh Sep 12 '12 at 06:53
Alright, I interpreted your comment as a solution to OP:s problem. Thanks for the pointers anyway. – alexn Sep 12 '12 at 07:00

score 0 · Answer 3 · answered Sep 12 '12 at 06:26

This is not possible this way. You could try it with a nested select statement, somewhat like this:

DELETE FROM table
WHERE type = 'test'
AND ID IN (SELECT id from table where type = 'test' order by id desc limit 30 )

score 0 · Answer 4 · answered Sep 12 '12 at 06:26

0

Try like this

DELETE FROM table WHERE id in(SELECT id FROM table WHERE type = "test" order by id desc limit 30, 60)

answered Sep 12 '12 at 06:26

GautamD31

28,552
10
64
85

score 0 · Answer 5 · answered Sep 04 '17 at 09:50

I was unable to use the limit clause in the sub-query, so the solution I use, somewhat messy, is:-

select group_concat(id) into @idList from
( 
select id from  table order by id desc limit 0,30
) as saveIds;
delete from table where not find_in_set(id,@idList)

Alternatively,

select group_concat(id) into @idList from
( 
select id from  table order by id desc limit 30
) as saveIds;
delete from table where find_in_set(id,@idList)

Mysql delete order by

5 Answers5

Linked