deleting variables by specifying id value with OFFSET

Question

I want to delete specific variables based on 'id' value. but the code below is displaying syntax error near: OFFSET 1. I use a similar code where I use SELECT instead of DELETE and it works fine, What am doing wrong here? Thanks

DELETE  FROM users WHERE name = '$name' ORDER BY id ASC LIMIT 1 OFFSET 1

please explain your question in more detail.what you are trying?? — Jalpesh Patel, Aug 01 '12 at 12:51
I don't believe [`DELETE`](http://dev.mysql.com/doc/refman/5.0/en/delete.html) supports the use of the `OFFSET` keyword directly. — Ricardo Altamirano, Aug 01 '12 at 12:52

score 0 · Answer 1 · answered Aug 01 '12 at 12:52

0

You cannot specify offset in DELETE's LIMIT clause.

Simple use:

DELETE  FROM users WHERE name = '$name' ORDER BY id ASC LIMIT 1;

answered Aug 01 '12 at 12:52

metalfight - user868766

2,722
1
16
20

but then it will delete the lowest $name value not the second lowest hence the need for the OFFSET – user1559811 Aug 01 '12 at 12:55
http://stackoverflow.com/questions/7142097/mysql-delete-statement-with-limit -- look here. – metalfight - user868766 Aug 01 '12 at 13:00

score 0 · Answer 2 · answered Aug 01 '12 at 12:52

0

Try

DELETE  FROM users WHERE name = '$name' ORDER BY id ASC LIMIT 1,1

answered Aug 01 '12 at 12:52

Madhivanan

13,470
1
24
29

score 0 · Answer 3 · answered Aug 01 '12 at 12:53

0

You can not have OFFSET in DELETE query.

answered Aug 01 '12 at 12:53

jsist

5,223
3
28
43

is there some alternative that will have the same effect? – user1559811 Aug 01 '12 at 12:55

score 0 · Accepted Answer · answered Aug 01 '12 at 12:54

0

The offset component in LIMIT is not available in MySQL DELETE statements but it is allowed in SELECT statements.

So what you can do to get around this fact, is you can actually join a subselect in a DELETE operation, which will then give you your desired results:

DELETE a FROM users a
INNER JOIN
(
    SELECT id
    FROM users
    WHERE name = '$name'
    ORDER BY id
    LIMIT 1,1
) b ON a.id = b.id

answered Aug 01 '12 at 12:54

Zane Bien

22,685
6
45
57

oh, sorry did I not accept yours, I thought it was I selected. I have accepted it now. – user1559811 Aug 01 '12 at 13:31

deleting variables by specifying id value with OFFSET

4 Answers4