MySQL Delete with Limit

Question

This seems very simple, but it's failing. All I want to do is delete all but the first row using the code below.

$sql    = "DELETE FROM ".TABLE_PREFIX."news WHERE course_id = $course_id LIMIT 1,18446744073709551615";
$result = mysql_query($sql, $db);

If I do this...

$sql    = "SELECT news_id FROM ".TABLE_PREFIX."news WHERE course_id = $course_id LIMIT 1,18446744073709551615";
$result = mysql_query($sql, $db);
while ($row = mysql_fetch_assoc($result)) {
    echo $row['news_id'] . '<br>';
}

All news_ids are echoed except the first one. Why is the delete statement not working when the same statement for select is working?

Answer 1

Like @Sabashan said LIMIT in DELETE statement only allows one parameter.

If you want to exclude the 1st row, use something like:

DELETE FROM tnews WHERE course_id = $course_id 
AND primary_key NOT IN 
(SELECT * FROM (
SELECT primary_key FROM tnews ORDER BY something LIMIT 1 OFFSET 0))

The inner SELECT picks up the records to exclude. The outer SELECT solves the problem that you cannot select from the same table that you're deleting from. Because the inner SELECT is enclosed by the outer select MySQL materializes the result in a temp table and uses that instead of rerunning the query for every delete (which MySQL does not allow).