How to randomly delete 20% of the rows in a SQLite table

Question

Good afternoon, We were wondering how to randomly delete 20% of the rows in a sqlite table with 15000 rows. We noticed that this question was solved in Stack Overflow using SQL Server Select n random rows from SQL Server table. But the SQL Server script does not appear to function properly in sqlite. How can we convert the SQL Server script to an sqlite equivalent script? Thank you.

Answer 1

Alternatively, since the random() function in sqlite returns a signed 64-bit integer, we can calculate a point within this space as (2^63) * 0.6 . Signed integers greater than this will be 40% of the set of positive signed 64-bit integers, so 20% of the whole set.

Truncate to the integer below, this is 5534023222112865484 .

Therefore you should be able to get 20% of your rows with a simple:

   SELECT * FROM table WHERE random() > 5534023222112865485

Or in your case, since you want to delete that many:

   DELETE FROM table WHERE random() > 5534023222112865485

I hope you enjoy this approach. It may actually be suitable if you want high performance from such an operation, but it could be hardware dependent / version dependent, so probably is not worth the risk.