1

I was wondering if there is a way to delete everything but the first 100 rows in a a table in a mysql database. Can this be done using the phpmyadmin GUI.

seus
  • 568
  • 9
  • 31
  • @FastSnail So you can select 100 you want to KEEP and delete the rest ? – seus Jul 15 '16 at 02:40
  • 1
    something like: `delete from foo where id >100` –  Jul 15 '16 at 02:40
  • 1
    @seus sorry i misread it.i think it's hard to do in gui.but you can edit mysql query in the gui .check this question http://stackoverflow.com/questions/17292375/delete-all-rows-except-first-n-from-a-table-having-single-column – Madhawa Priyashantha Jul 15 '16 at 02:46
  • @Dragon how do i cascade deletes with all foreign keys? – seus Jul 15 '16 at 02:50

3 Answers3

3

Yes, it is possible, depending what you mean by "first 100". I'm assuming you have an auto-increment primary key column (e.g. id) and the "first 100" rows would be the rows that show up first when ordered by that column. If so, to find the last id of the first 100 rows, you'd write:

SELECT MAX(m.id) FROM (SELECT id FROM mytable ORDER BY id LIMIT 100) m

Now, you want to delete any rows with the id greater than this max, so you write:

DELETE FROM
  mytable
WHERE
  id > (SELECT MAX(m.id)
        FROM
          (SELECT id FROM mytable ORDER BY id LIMIT 100) m);

Here's the sqlfiddle that's demonstrating this for 5 rows.

Unix One
  • 1,151
  • 7
  • 14
1

Can I suggest you alternative approach. Let me know if it works for you?

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable LIMIT 100;

It will a create new table with first 100 records, this way you will keep backup of old table also. if you want it then keep it or delete it.

Shrikant Mavlankar
  • 1,145
  • 1
  • 8
  • 17
0

try this:

Delete from table where id > 0 and id <101

this will delete the record from 1 to 100 unless you create a loop of it..

loop will do in stored proc i guess..

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30