Delete random rows in table but left fixed amount of entries

Question

I have a table with unknow number of rows in it. I need to remove all the rows, except a fixed number of records.

Example: Table 1 has 439 rows. I need to keep 200 rows randomly and delete others.

The "logic way" is:

DELETE FROM table_1 
WHERE id NOT IN (SELECT id FROM table_1 ORDER BY RAND() LIMIT 200)```

But mariaDB says

You cannot use LIMIT in subquery ...

How I can do it? Thanks in advance!

What does `SELECT VERSION();` return? – Bill Karwin Oct 15 '22 at 16:01 — Bill Karwin, Oct 15 '22 at 16:01
10.6.7-MariaDB-1:10.6.7+maria~buster – Matt Ross Oct 15 '22 at 16:03 — Matt Ross, Oct 15 '22 at 16:03

score 3 · Accepted Answer · answered Oct 15 '22 at 16:11

3

Here's a solution tested on MariaDB 10.6:

delete t1 from table_1 as t1
left join (select id from table_1 order by rand() limit 200) as t2
using (id)
where t2.id is null;

answered Oct 15 '22 at 16:11

Bill Karwin

1 Answers1