sqlmariadbmariadb-10.6

Delete random rows in table but left fixed amount of entries

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!

Solution

  • 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;

    Demo: https://dbfiddle.uk/zygpZ0u0