CODEWITHSUNDEEP
mysqlsql
Roman
Roman

Reputation: 10403

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

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?

Upvotes: 9

Views: 5966

Answers (3)

Rick James
Rick James

Reputation: 142560

When deleting a lot of rows, this is an efficient trick:

CREATE TABLE new LIKE real;   -- empty table with same schema
INSERT INTO new SELECT * FROM real ... LIMIT 10;  -- copy the rows to _keep_
RENAME TABLE real TO old, new TO real;  -- rearrange
DROP TABLE old;  -- clean up.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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

Upvotes: 4

Déjà vu
Déjà vu

Reputation: 28850

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;

Upvotes: 8

Related Questions