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

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 Stac开发者_如何学Gok 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.

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.

Not quite 'random' - but if you've an identity column on the table you could DELETE FROM mytable WHERE ID % 5 = 0 which should statistically delete very close to a fifth of the rows.

Try:

DELETE FROM TABLE 
WHERE ROWID IN (SELECT ROWID FROM TABLE ORDER BY RANDOM() LIMIT 3000) 

If you want to calculate 20% in a subquery:LIMIT (SELECT CAST( ( COUNT(id) * 0.2 ) AS INT )

SQLite - ORDER BY RAND() provides a hint. Thus this may work?

DELETE FROM table WHERE id IN(
    SELECT id FROM table ORDER BY RANDOM() LIMIT (
        SELECT CAST( ( COUNT(id) * 0.2 ) AS INT ) FROM table
    )
);