SQL delete if query returns more than 2 rows-CodePudding

I've just inserted new url and I want to delete the old one.

I need to check if there are 2 same, if so, delete the old one.

If there is only one, do nothing.

DELETE FROM files
WHERE url
IN (SELECT id FROM files WHERE url='$url' ORDER BY date ASC LIMIT 1)

I'm getting this error:

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

Can you help me please? Thanks.

CodePudding user response：

First you need to get duplicated urls and then find the first Id of it by using Row_Number() like this:


DELETE FROM files
WHERE id IN (
SELECT t.id
FROM
(
SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY url ORDER BY date ASC) rw
    FROM files
    WHERE url IN (
                            SELECT url
                            FROM files
                            GROUP BY url
                            HAVING COUNT(*) > 1
                        )
) t
WHERE t.rw = 1
)