CODEWITHSUNDEEP
mysqlsql-delete
Dusan
Dusan

Reputation: 3478

Leave only first 50 records in SQL database and delete the rest

I have a table of scores, which is made of 2 fields: name and high score. Something like this:

----------------------- 
| name     | score    |
-----------------------
| John     | 2450     |
-----------------------
| Alice    | 2420     |
-----------------------
                         ... etc

I need to delete all the rows till the top 50 scores.

Is it possible without creating another temporary table ?

Upvotes: 4

Views: 4323

Answers (3)

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

You need a unique field for this , so either alter the table before and after the delete.

Like : alter table scores add id int unique auto_increment not null;

delete from scores where id > 50;

alter table drop column id;

Upvotes: 0

Yograj Sudewad
Yograj Sudewad

Reputation: 343

please try this

delete from scores_tbl Where
id not in
(select * from
(select id from scores_tbl order by score desc limit 50)
 as temp)

Upvotes: 7

Linga
Linga

Reputation: 10553

Create an auto increment field

alter table scores add id int unique auto_increment not null;

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

select * scores;
delete from scores where id > 50;

Finally, remove that field

alter table scores drop id;

Upvotes: 1

Related Questions