DELETE with a percentage on total count

mysql, sql

Solution

I would simply return the total amount of filtered rows, calculate through php and use that 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'm not sure if DELETE allows an advanced query such as 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

Problem

Let's say I want to delete 10% of rows, is there a query to do this? Something like: ``` DELETE FROM tbl WHERE conditions LIMIT (SELECT COUNT(*) FROM tbl WHERE conditions) * 0.1 ```

Original source