CODEWITHSUNDEEP
mysqlrandomconditional-statementssql-delete
yossi
yossi

Reputation: 3164

Random delete of unknown number of records

I have a table, named items.
The structure is: {name, id, category_id}

There are unknown number of records in the table, I need to limit the number of records for each category to a MAX number, 20 for example.

Pseudo

DELETE FROM items
WHERE (SELECT COUNT(category_id) FROM items)> 20
LIMIT (SELECT COUNT(category_id) FROM items) - 20

Upvotes: 2

Views: 452

Answers (2)

Michael
Michael

Reputation: 10474

If you have a large table, the most efficient way is to recreate that table:

create table items_temp like items;
INSERT into items_temp (id, category_id, ... ) 
  SELECT id, category_id, ... from items orig
  WHERE id in (
     SELECT id from items items_select
     WHERE orig.category_id = items_select.category_id
     ORDER by category_id LIMIT 20
     );
RENAME table items to items_old, items_temp to items;

EDIT: TRUNCATE table items_old Don't DELETE - that is what we are trying to avoid - deletions will actually delete each row and adhere to foreign constraints, which is what will take so much cpu and IO, truncate does not do this.

Otherwise, you will most likely take down your database during the deletion phase. If it is a small amount of data, no big deal.

Upvotes: 3

solaimuruganv
solaimuruganv

Reputation: 29807

DELETE FROM items 
WHERE (category_id,id) not in (
  SELECT category_id, id FROM items  a WHERE id IN (
     SELECT id FROM items s 
     WHERE a.category_id = s.category_id
     ORDER BY category_id LIMIT 20) 
  ORDER BY  id)

Upvotes: 3

Related Questions