MySQL remove duplicate rows with two same columns

Question

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.

what would the result be exactly from the example data? how do you know what you would keep? (what happens with the other columns?) — gloomy.penguin, Nov 06 '13 at 17:07
I just want to save one copy, only the id is different and i dont care about that, in this example, remove last row — JTC, Nov 06 '13 at 17:15
try the answer posted here: http://stackoverflow.com/a/3312098/623952 or here http://stackoverflow.com/a/4685232/623952 — gloomy.penguin, Nov 06 '13 at 17:53

score 1 · Answer 1 · answered Nov 06 '13 at 17:07

1

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

answered Nov 06 '13 at 17:07

Mihai

26,325
7
66
81

score 1 · Answer 2 · answered Nov 06 '13 at 17:07

1

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

answered Nov 06 '13 at 17:07

Arun Killu

13,581
5
34
61

`count(*)>=2`... would that delete all records of things with more than one occurrence? i think OP wants to save one copy. – gloomy.penguin Nov 06 '13 at 17:08
1

Your answer is giving me 1093 error Can't specify target table for update in FROM clause – JTC Nov 06 '13 at 17:24
1

It WILL delete ALL your duplicates. – Mihai Nov 06 '13 at 17:32
you still need to change the `count(*)>2` to `>=2` otherwise it will only work for things with 3 or more duplicates... – gloomy.penguin Nov 06 '13 at 17:55

score 0 · Answer 3 · answered Nov 06 '13 at 17:15

0

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

answered Nov 06 '13 at 17:15

OGHaza

4,795
7
23
29

mysql doesn't do `partition by` – gloomy.penguin Nov 06 '13 at 17:52

MySQL remove duplicate rows with two same columns

3 Answers3