DELETE with percentage of total

Question

Say I want to remove 10% of the rows, is there a query for this?

Sort of:

DELETE FROM tbl WHERE conditions LIMIT (SELECT COUNT(*) FROM tbl WHERE conditions) * 0.1 

Answer 1

I would just return the total number of filtered rows, calculate via php and use this value as a limit in my DELETE query.

 $query = mysql_query("SELECT COUNT(*) FROM tbl WHERE conditions"); $int = reset(mysql_fetch_array($query)); $int = round($int * 0.1); mysql_query("DELETE FROM tbl WHERE conditions LIMIT {$int}"); 

I am not sure if DELETE allows an advanced query like this one:

 DELETE FROM ( SELECT h2.id FROM ( SELECT COUNT(*) AS total FROM tbl WHERE conditions) AS h JOIN ( SELECT *, @rownum := @rownum + 1 AS rownum FROM tbl, (SELECT @rownum := 0) AS vars WHERE conditions) AS h2 ON '1' WHERE rownum < total * 0.1) AS h3 

Answer 2

If you need approximately 10% of the lines, in a specific order, this should do the trick:

 DELETE FROM tbl WHERE RAND() <= 0.1 

However, I do not recommend using it on very large datasets due to the overhead of generating random numbers.