How to delete the first line suitable for the requirements? - MySQL (MariaDB)

user_19315652 0
Viewed 38

I have such a code,

DELETE FROM queue WHERE id IN
(SELECT id FROM queue WHERE id=%s LIMIT 1)

but when starting, such an error appears

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Can you tell me how to do without a tag IN or to realize it at all differently? Thanks.

mariadb sql-delete limit mysql
2 Answers

You might be able to get away with phrasing your logic as a delete join:

DELETE q1
FROM queue q1
INNER JOIN
(
    SELECT id
    FROM queue
    WHERE id = %s
    -- ORDER BY some column here
    LIMIT 1
) q2
    ON q2.id = q1.id;

But note that if you are using LIMIT, you should really also be using ORDER BY. I have added a placeholder in the subquery for an ORDER BY clause.

user_18632290

MariaDB and MySql allow an ORDER BY clause (which you should use) and LIMIT in a DELETE statement:

DELETE FROM queue 
WHERE id = %s
-- ORDER BY ....
LIMIT 1;
user_104988280
Related