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?

Also, that's not the right syntax for LIMIT in a DELETE statement - you just need to include one integer for the maximum number of rows to delete: http://dev.mysql.com/doc/refman/5.0/en/delete.html — andrewsi, Jul 05 '13 at 18:51
The "LIMIT 1,18446744073709551615" part is supposed to be skipping the first row, then deleting everything else. I got this idea from here: http://stackoverflow.com/questions/2827029/mysql-skip-first-10-results — gtilflm, Jul 05 '13 at 18:56
Whatever you do, I advice defining `ORDER BY news_id ASC` so that it will always be sorted the way you think it is sorted. I have never tried to delete like that, so I can't give much help for that. You could possibly select the first news_id, then delete `WHERE course_id = $course_id AND news_id != $news_id` — Sumurai8, Jul 05 '13 at 18:56
@JohnBovey - that question is about SELECT, not DELETE. The two syntaxes are similar, but not identical. — andrewsi, Jul 05 '13 at 19:08
do you need to delete all rows except one, no matter what, or there's a particular row that is the first? — fthiella, Jul 05 '13 at 19:11
news_id is an auto incrementing field. So, basically the lowest news_id for a particular course would be the "first" one and it should not be deleted. Does that help define this problem a bit more? — gtilflm, Jul 05 '13 at 19:20

score 4 · Answer 1 · answered Jul 05 '13 at 19:13

4

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

answered Jul 05 '13 at 19:13

Johan

74,508
24
191
319

I'm trying to implement this, but it's not working. I have `$sql = "DELETE FROM ".TABLE_PREFIX."news WHERE course_id = $course_id AND primary_key NOT IN (SELECT * FROM (SELECT primary_key FROM ".TABLE_PREFIX."news ORDER BY news_id LIMIT 1 OFFSET 0))"; $result = mysql_query($sql, $db);` Thoughts? – gtilflm Jul 05 '13 at 19:58
You need to replace the ’primary_key’ placeholder with whatever field name you pk has. If there really was a keyword like that, I'd written it in caps as per convention. – Johan Jul 06 '13 at 09:23

Sabashan Ragavan · Accepted Answer · 2013-07-05T19:08:08.237

2

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.";

edited Jul 05 '13 at 19:08

answered Jul 05 '13 at 18:55

Sabashan Ragavan

728
1
5
14

The examples do not equal the OP's code. And the also do not exclude all but the first row. – Johan Jul 05 '13 at 19:20
I can see this working by first selecting the "first" news_id as I described in the comment above. I'll hold off on selecting this as the answer until others have had a chance to reply though. Thanks! – gtilflm Jul 05 '13 at 19:21
After some tweaking, I got your idea working. If anyone is interested, the code I ended up using was `$sql = "SELECT news_id FROM ".TABLE_PREFIX."news WHERE course_id = $course_id ORDER BY news_id asc LIMIT 1"; $result = mysql_query($sql, $db); $row = mysql_fetch_assoc($result); $sql = "DELETE FROM ".TABLE_PREFIX."news WHERE course_id = $course_id AND news_id!='".$row['news_id']."'"; $result = mysql_query($sql, $db);` – gtilflm Jul 05 '13 at 20:16

score 0 · Answer 3 · answered Jul 05 '13 at 19:01

0

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)

answered Jul 05 '13 at 19:01

Mr.Web

6,992
8
51
86

MySQL Delete with Limit

3 Answers3