CODEWITHSUNDEEP
phpmysql
gtilflm
gtilflm

Reputation: 1475

MySQL Delete with Limit

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?

Upvotes: 3

Views: 10538

Answers (3)

Johan
Johan

Reputation: 76753

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).

Upvotes: 4

Sabashan Ragavan
Sabashan Ragavan

Reputation: 738

The delete statement is not working because of incorrect syntax near the limit keyword...you cannot do LIMIT 1,18446744073709551615...LIMIT must be followed by a single number when used with DEELTE

Reference: DELETE MYSQL

Only in a SELECT statement can you have LIMIT 1,18446744073709551615 clause

Reference: SELECT MYSQL

What you can do alternatively is find the course_id of the first row you want deleted, (and hopefully if the table is set up correctly and it is a primary key for the table and auto increments), do something like the below: 

$sql = "DELETE FROM ".TABLE_PREFIX."news 
        WHERE course_id >=".COURSE_ID_YOU_WANT_DELETED.";

Or this works as well:

$sql = "DELETE FROM ".TABLE_PREFIX."news 
        WHERE course_id !=".COURSE_ID_YOU_WANT.";

Upvotes: 2

Mr.Web
Mr.Web

Reputation: 7176

You should find the id of the first row and do a 

DELETE FROM table WHERE id != id_found

or find the ids you have to delete and

DELETE FROM table WHERE id IN (ids_found)

Upvotes: 0

Related Questions