CODEWITHSUNDEEP
mariadbsql-delete
ehime
ehime

Reputation: 8405

Remove anything above 900 rows in MariaDB

This works in Mysql, but it seems that the syntax for MariaDB is different, I am trying to remove anything above the first 900 returned rows (LIMIT 900) :

DELETE FROM cronschedule NOT IN (SELECT * FROM cronschedule LIMIT 900);

Trying to do this in Maria though returns the following error :

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near 
'NOT IN (SELECT * FROM cronschedule LIMIT 900)' at line 1

So how would I do this in Maria?

Upvotes: 0

Views: 678

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

I'd expect this to be a bit more efficient than your answer with the LEFT JOIN / IS NULL construction:

DELETE  cr.*
FROM    cronschedule cr
    JOIN
        (
        SELECT  id
        FROM    cronschedule ii
        ORDER BY
                id ASC
        LIMIT 1 OFFSET 900
        ) i2
ON      cr.id >= i2.id ;

Upvotes: 2

ehime
ehime

Reputation: 8405

This seems to work

DELETE  cr.*
FROM    cronschedule cr
LEFT JOIN
        (
        SELECT  id
        FROM    cronschedule ii
        ORDER BY
                id ASC
        LIMIT 900
        ) i2
ON      cr.id = i2.id
WHERE   i2.id IS NULL;

Upvotes: 1

Related Questions