Reputation: 1475
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
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
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
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