How to delete every record after the nth row in mysql?

Question

In mysql I can query select * ... LIMIT 10, 30 where 10 represents the number of records to skip.

Does anyone know how I can do the same thing in delete statements where every record after the first 10 records get deleted?

Answer 1

Considering there is no rowId in MySQL (like in Oracle), I would suggest the following:

alter table mytable add id int unique auto_increment not null;

This will automatically number your rows in the order of a select statement without conditions or order-by.

select * from mytable;

Then, after checking the order is consistent with your needs (and maybe a dump of the table)

delete from mytable where id > 10;

Finally, you may want to remove that field

alter table mytable drop id;

Answer 2

The following will NOT work:

DELETE 
FROM table_name 
WHERE id IN
  ( SELECT id
    FROM table_name
    ORDER BY          --- whatever
    LIMIT 10, 30
  ) 

But this will:

DELETE 
FROM table_name 
WHERE id IN
  ( SELECT id
    FROM 
      ( SELECT id
        FROM table_name
        ORDER BY          --- whatever
        LIMIT 10, 30
      ) AS tmp
  ) 

And this too:

DELETE table_name 
FROM table_name 
  JOIN
    ( SELECT id
      FROM table_name
      ORDER BY          --- whatever
      LIMIT 10, 30
    ) AS tmp
    ON tmp.id = table_name.id