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.
ALTER IGNORE TABLE tableName
ADD CONSTRAINT SomeName UNIQUE (name ,attribute_id)
DELETE
FROM tbl
WHERE id IN (
SELECT id from tbl
GROUP BY name,attribute_id
HAVING COUNT(*)>2
)
count(*)>=2... would that delete all records of things with more than one occurrence? i think OP wants to save one copy.
count(*)>2 to >=2 otherwise it will only work for things with 3 or more duplicates...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
partition by