CODEWITHSUNDEEP
sqlmariadbmariadb-10.6
Matt Ross
Matt Ross

Reputation: 79

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!

Upvotes: 2

Views: 167

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562711

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

Upvotes: 3

Related Questions