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?
3 Answers
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; 
2 Comments
Nanne
Some questions: Is it defined how adding an
auto_increment will number when altering a table like this? Would it keep to the needed order, and if so when testing: is this "coincidence" or defined behaviour? Also: does mysql reset the auto_increment number after dropping the column? If not, you might want to explicitly set that, or it might keep on counting. And finally: how expencive would this become for big tables?
Rick James
If
auto_increment_increment is not 1 (in a multi-master setup), this will not work quite correctly. Also, it will take a long time to run if the table is big.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 
1 Comment
Rick James
I think the reason for "not work" is that the same table is mentioned directly.
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. 
DELETE FROM TABLE WHERE ID IN (SELECT ID FROM TABLE LIMIT 10, 30). @Am commented that that does not work in "this version of MySQL". So although it isn't the answer to the question, it might be helpful for other answerers :)ORDER BY.