MySQL Question

[Cliphead]

I have several tables containing over 20k rows. Is there a quick way to delete all but the first 20 rows in each of the tables?

[lilelvis2000]

I'm not a mySQL guru but is there a way to spit out the rownum?
Something like select @rownum:=@rownum+1, ID, ..... from ... where @rownum <=20;

insert that into a temporary table and then
delete from tableA where ID not in (select ID from tmptable)

[Cliphead]

I'm not a mySQL guru but is there a way to spit out the rownum?
Something like select @rownum:=@rownum+1, ID, ..... from ... where @rownum <=20;

insert that into a temporary table and then
delete from tableA where ID not in (select ID from tmptable)

Thanks I'll give that a go.

[FiveTimes]

Can you not use the order by limit 20 in the delete statement ?

[Cliphead]

Can you not use the order by limit 20 in the delete statement ?

I'm clueless when it comes to sql

[eek]

I have several tables containing over 20k rows. Is there a quick way to delete all but the first 20 rows in each of the tables?

well this will give you the first 20 rows

SELECT tableid FROM {table} ORDER BY {row} asc LIMIT 20

so something like will work
delete from table where tableid not in (
SELECT tableid FROM {table} ORDER BY {row} asc LIMIT 20)

[lilelvis2000]

Good stuff.

[administrator]

How many tables? Do the tables have an ID column? As other posters have said you can simply delete where ID > 20 so

DELETE from TABLE where ID > 20;

[2BIT]

I'm clueless when it comes to sql

dunno mysql but in Oracle you could use the rownum, mysql prob has something similar,

or perhaps SELECT * FROM TABLE WHERE NOT IN (SELECT TOP 20 * FROM TABLE)

[yorkshireman]

I have several tables containing over 20k rows. Is there a quick way to delete all but the first 20 rows in each of the tables?

Use phpmyadmin or Mysql Workbench and select/delete with your mouse.