Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I received a database with a few million records in it, but apperently there might be duplicate records in them.

A user enters data into the database and a primary key is generated, however if the user enters the same data again, a new primary key will be generated for that data, even though the data has already been entered before. There are no checks on this.

I need to go looking for these duplicates, but I do not really know where to start. I first thought concatenating all cells except the primary key in a subquery and then count these rows and see which ones have a count higher than 1.

cfr.

pkey    recipe     fkey    comment
1   toast       3       tasty
2   curry       2       spicy
3   curry       2       spicy
4   bread       1           crumbly
5   orios       2       cookies

Here the curry entries are identical and I'd have to delete 1 of those.

However I read concatenating is unpredictable in mysql and it just feels a bit wrong to me as well.

Any hints ?

share|improve this question
3  
There are about 12 million questions and answers on Stack Overflow about finding dupes in SQL. Have you checked there? –  JNK Feb 19 '12 at 13:07
2  
I think we need to keep this here so we can close future dupes without sending them to SO. –  Jack Douglas Feb 19 '12 at 13:13
    
What's the best way to handle it since there are dupes on SO? Make an answer with SO links? –  JNK Feb 19 '12 at 13:14
    
Ow I didn't check stack, I assumed it would be on here. Sorry, still it might be interesting to have here, so if you copy an answer of stack that sovles it I'll accept it. –  Lucas Kauffman Feb 19 '12 at 15:18
    
I deleted my answer - I can't come up with the query that will update your child tables foreign keys based on the duplicate-removed recipe tables new primary ids. You could set the constraints to cascade, but you will loose comments for duplicates that are removed in the recipe table. –  Jeff Feb 19 '12 at 17:51

1 Answer 1

up vote 1 down vote accepted

Suppose your table is called ingredients. Try the following:

Step 01) Create an empty delete keys table called ingredients_delete_keys

CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;

Step 02) Create PRIMARY KEY on ingredients_delete_keys

ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);

Step 03) Index the ingredients table with fk,recipe,pkey

ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);

Step 04) Populate the ingredients_delete_keys table

INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;

Step 05) Perform a DELETE JOIN on ingredients table using keys that don't match

DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;

Step 06) Drop the delete keys

DROP TABLE ingredients_delete_keys;

Step 07) Get rid of the fk_recipe_pkey_ndx index

ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;

OK Here are all the lines in one block...

CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;
ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);
ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);
INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;
DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;
DROP TABLE ingredients_delete_keys;
ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;

Give it a Try !!!

CAVEAT

Notice that using MIN function helps keep the first pkey entered for fk. If you switch it to MAX function instead, the last pkey entered for fk is kept.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.