Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql delete order by

Tags:

sql

mysql

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?

like image 695
Abby E Avatar asked Sep 12 '12 06:09

Abby E


2 Answers

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
        )
like image 60
John Woo Avatar answered Sep 21 '22 13:09

John Woo


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)
like image 26
alexn Avatar answered Sep 17 '22 13:09

alexn