Delete all rows per group except one using CTE-CodePudding

I'm using MariaDB, and I am trying to make two things, both are failing.

(1) I'm trying to delete all duplicated items, but maintaining one record.

WITH CTE AS (
  SELECT asin, ROW_NUMBER() OVER (PARTITION BY asin ORDER BY created_at) AS n
  FROM asin_list
)
DELETE
FROM CTE
WHERE n > 1

This returns the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB.

(2) As a workaround from above query I was trying to insert all duplicated ASINs into a table, having as a goal to select max(asin) later on and delete it.

WITH CTE AS (
  SELECT asin, ROW_NUMBER() OVER (PARTITION BY asin ORDER BY created_at) AS n
  FROM asin_list
)
INSERT INTO temp1 *
FROM FROM CTE
WHERE n > 1

But this returns the same error. Can you please, help me fixing this?

CodePudding user response：

You could write the statement as:

select * -- delete
from asin_list as newer
where exists (
    select *
    from asin_list as older
    where older.asin = newer.asin
    and older.created_at < newer.created_at -- don't use <= or it'll match everything
)

CodePudding user response：

Try to add “;” before “WITH”. Something like:

;WITH CTE AS ( SELECT asin , row_number() OVER(PARTITION BY asin ORDER BY asin_list.created_at) AS n FROM asin_list ) delete from CTE WHERE n > 1

Let me know