1

I'm having a tough time finding a query to delete a specific number of rows in a table. My goal is to keep the number of rows in a table to a specific number. For example, I don't want to exceed 1 million rows. Isn't there a way to add a trigger on insert that looks at the row count and then deletes the number of rows that exceeds 1 million? Can someone please point me in the right direction?

I tried:

delete from `table` where `id` not in
(select `id` from `table` order by `id` desc limit 20)

But, I get error code 1235, this version of mysql doesn't yet support 'limit & in/all/any/some subquery'.

Any help is much appreciated.

James
  • 31
  • 6
  • It should be better do that with 2 queries. One for counting the total and the second one to delete with a limit =(total - 1million) – Robert May 11 '17 at 01:44
  • Robert, I was trying that too, but I wasn't able to get that to work either. I thought I could create a variable and set the value = count. Then use this variable in the next query, but I wasn't able. If you couldn't tell, I'm not very experience in sql stuff. Any chance you could show an example? – James May 11 '17 at 02:14
  • Are you programming only in SQL or do you have something like PHP or Java? – Robert May 11 '17 at 02:17
  • My application is vb.net. If I can't get it done in MySQL I will just do it all in .net. – James May 11 '17 at 13:05
  • So it looks like a trigger is not possible, http://stackoverflow.com/questions/5885715/mysql-triggers-to-delete-child-records-in-the-same-table – James May 11 '17 at 17:19
  • It looks like you can't use a variable in a limit either, however, it looks like there is a workaround: https://orkus.wordpress.com/2010/06/29/variable-limit-statement-in-mysql/. So, I think I have a solution. – James May 11 '17 at 17:40
  • I just did all of this in vb.net. I found it easier that way. – James May 11 '17 at 18:48

1 Answers1

0

how about doing it by batch like

DELETE FROM table WHERE id BETWEEN 1 AND 100

and just add a for loop on it.

Melvin Angelo Jabonillo
  • 59
  • 3