CODEWITHSUNDEEP
mysql
JTC
JTC

Reputation: 3464

MySQL remove duplicate rows with two same columns

I've got table with following columns: 

id,name,attribute_id,params_desc

The data in table looks like this:

0,'some',1,'something'
1,'some',2,'somethingelse'
2,'some',3,'somethingelses'
3,'some',1,'something'

What I need is to remove duplicates, which have the same name and attribute_id.

I was unable to find some working solution here, as DISTINCT or UNIQUE INDEX or INSERT IGNORE did not work for me.

Thx for your time.

Upvotes: 1

Views: 617

Answers (3)

OGHaza
OGHaza

Reputation: 4795

Apologies since I'm not sure the syntax is the same in MySQL as in SQLServer - however a quick google suggests it might be, otherwise perhaps this will point you in the right direction:

DELETE a FROM 
(
    SELECT id
            ,name
            ,attribute_id
            ,params_desc
            ,row = ROW_NUMBER() OVER (PARTITION BY name, attribute_id ORDER BY id ASC)
    FROM Table
) a
WHERE row > 1

Upvotes: 0

Arun Killu
Arun Killu

Reputation: 14233

DELETE 
FROM tbl 
WHERE id IN (
SELECT id from tbl 
GROUP BY name,attribute_id 
HAVING COUNT(*)>2
)

Upvotes: 1

Mihai
Mihai

Reputation: 26784

ALTER IGNORE TABLE tableName
ADD CONSTRAINT SomeName UNIQUE (name ,attribute_id)

Upvotes: 1

Related Questions