SQL delete if query returns more than 2 rows

Question

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.

Answer 1

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
)

Answer 2

The task is rather simple: you want to delete files for which exists a newer entry.

DELETE FROM files
WHERE EXISTS
(
  SELECT NULL
  FROM files newer
  WHERE newer.url = files.url AND newer.date > files.date
);

An index to support this statement would look like this:

CREATE INDEX idx ON files (url, date);

The above would be my preferred approach. But there are other methods of course. For instance:

DELETE FROM files
WHERE (url, date) NOT IN
(
  SELECT url, MAX(date)
  FROM files
  GROUP BY url
);

It's the same index that would help this statement, too.

Both statements remove all duplicates, no matter whether you have two entries for a URL or hundreds.

If this is really only about deleting the oldest row, however, (because there cannot be more than two entries for a URL or because you want to keep duplicates except for the oldest), this gets faster this way:

DELETE FROM files
WHERE (url, date) IN
(
  SELECT url, MIN(id)
  FROM files
  -- WHERE url = @url -- add this, if this is only about one URL
  GROUP BY url
  HAVING COUNT(*) > 1
);