I ve a Sql table (mariaDB) with 2 Primary keys. I want to remove the rows where the first primary key is duplicated.(yes i know that primary keys cant be duplicated but with 2 Primary keys they work like a touple so that it is possible, but in my case not wanted) example:

and i want to remove the 2nd line cause the id key is duplicated.

tried: almost any delete query with row count the query i tried last:

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS RN
FROM product_names
)

DELETE FROM CTE WHERE RN<>1

CodePudding user response：

To clarify the definition, you cannot have two primary key in a table. But the primary key of your table is composed of two columns.

To improve your schema, you may want to alter your table so that the primary key is only based on first column. However, depending on the database engine, it can be usefule to keep your composite key. It may speed up query which retrieve the second column only from the primary key. In that case you may want to add a unique clause to the first colume of your primary key.

To cleanup youru table you can use that, but beware it doesn't have a filter on the second column, meaning any column with the same id can be deleted depending on its order.

WITH duplicated AS (
    SELECT id, name, row_number() OVER (PARTITION BY a) row_number
    FROM product_names
    ORDER BY name
)
DELETE FROM product_names
WHERE (a, b) IN (SELECT a, b FROM duplicated WHERE row_number > 1);