how to delete database table row depending upon the row count

Question

I want to delete table rows from xyz table where row count should be greater than 10, but I don't have any column that holds row position. I don't want to add additional column such as Id. Here is my table structure:

name(as text)  subject(as text)  filename(as text)

and here is my query which deletes entire table

delete from questions where (select Count(*) from questions)  between  10 and 20

I am not getting what to write instead of (select Count(*) from questions)

any solutions?

score 1 · Answer 1 · answered Mar 19 '12 at 12:33

1

Another solution might be to maintain a timestamp on each record and drop the oldest one when a new one comes in. It seems bizarre that you have so little need for the data you've persisted. I wonder about your requirements and your design.

answered Mar 19 '12 at 12:33

duffymo

305,152
44
369
561

score 1 · Accepted Answer · answered Mar 19 '12 at 12:38

1

I think this would work:

DELETE FROM table WHERE name NOT IN (SELECT TOP 10 name FROM table)

This is assuming that name is unique.

answered Mar 19 '12 at 12:38

Mike Bockus

2,079
17
23

If only you used LIMIT instead of TOP. :) Question is tagged MySQL. – Marcus Adams Mar 19 '12 at 12:40
thanks it works but, in sqlite I had to write this thing instead of SELECT TOP: SELECT * FROM Table_Name LIMIT 5 – Dhanesh Mar 19 '12 at 12:55
Ah, yes. I've been spending too much time using SQL Server... :-) – Mike Bockus Mar 19 '12 at 13:07

score 0 · Answer 3 · answered Mar 19 '12 at 12:37

0

Create a replica table with same scheme say test2

insert into test2 select * from test limit 10

delete from test;

insert into test select * from test2;

answered Mar 19 '12 at 12:37

Naveen Kumar

4,543
1
18
36

score 0 · Answer 4 · edited May 23 '17 at 10:08

0

MySQL does not provide a row id, but there seems to be a workaround using session variables, as posted in the answer to this thread: Equivalent of Oracle’s RowID in MySQL

edited May 23 '17 at 10:08

Community

1
1

answered Mar 19 '12 at 12:38

vaisakh

1,041
9
19

score 0 · Answer 5 · answered Mar 19 '12 at 12:53

Making 2 assumptions: name is you id column and you want to sort by name.

One way to do it would be to:

DELETE FROM Xyz WHERE name NOT IN (SELECT name FROM Xyz ORDER BY name LIMIT 10);

But that will likely just give you:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

As a workaround to that you can use a temporary table:

CREATE TEMPORARY TABLE temp SELECT * FROM Xyz ORDER BY name LIMIT 10;
DELETE FROM Xyz WHERE name NOT IN (SELECT name FROM temp);
DROP TABLE temp;

score 0 · Answer 6 · answered Mar 19 '12 at 13:00

This is weird.

1) Why don't you want an additional column?
2) Which rows do you want to delete? Unless the choice is made on name, subject or filename, you will need an additional column.
2a) If which rows are deleted does not matter, you can simply stop inserting after 10 rows. You can also have a CHECK constraint which enforces this for you, though I'm not sure if MySQL supports such an advanced construct.
3) Is the number really 10? If so, are you sure a database is the storage you need for this? I gotta go with @duffymo here, this smells of bad design.

how to delete database table row depending upon the row count

6 Answers6