How to Delete Duplicate Records?

In a MySQL (3.22.51) file I have three fields

idx (Auto Increment)
filename
pathname

I use an insert command in a dump.sql file to add new records to my file.

INSERT INTO jpeglist (idx, filename, pathname) VALUES ( '', 'c-purpsparkle26w-1.jpg', 'dennisj/20021007');

Sometimes I end up adding the same record twice.

How can I find all duplicate records in Field 'filename' and delete the extra records?

Can I change the INSERT INTO command so new records are added and duplicate records are only updated?

TIA

Dennis

Maybe you'll find this interesting?
http://www.mysql.com/doc/en/REPLACE.html

SELECT *, count(*) cnt FROM tableName GROUP BY fieldname1, fieldname2, .... HAVING cnt > 1

This will return all the records in the tableName more than 1 time. Include all the fields except the auto increment field in GROUP BY part.

Rest is trivial

Why not use a unique index on the table?

That way when it comes to actually inserting the record:
"INSERT INTO jpeglist (idx, filename, pathname) VALUES ( '', 'c-purpsparkle26w-1.jpg', 'dennisj/20021007');"
it will complain about this being a duplicate record and will not insert a new record. This will elliminate duplicate files without actually having to build any code for it?

Thanx
Clive

Every man has different problems vith files. I know how to solve the problem with duplicate files. I use Duplicate checker PRO 6.0 for such purposes (http://www.atory.com/Dupe_Checker_PRO/). I suggest you to try it if you experience "dupe-problem"...

Good luck!

You've exhumed a long-dead thread to pimp an irrelevant product (the problem is duplicate records in a database, not duplicate files).

Please don't do that.

Find and delete duplicate files and folders by Dupe Checker PRO

Deja vu.

I ran into a problem of duplicate files and someone told me about duplicates-finder.com

Hi. I ran into that problem too. And after days of searching I finally found this software: Delete Long Path File Tool.
It's GREAT. You can find it here:
longpathtool

Delete from table_name A
where rowid > (select min(rowid) from table_name B where B.keyword = A.keyword)

So you are looking for a application that delete values from database but for that purpose i think sql is fair enough to delete duplicate values and there are various constraints available in SQL that may help not to enter duplicate values in database.

Sometimes I end up adding the same record twice.

I use the program from DuplicateFilesDeleter.com to find and delete duplicates. You may try. Thanks