How do I remove duplicate data from two different table in Mysql

Question

I have two tables as follows

table 1 consist of only 1 column called FILE_ENCRYPTED(No primary key in table)

table 2 consists of only 1 column called FILE_DECRYPTED (No primary key in table)

Both the table contains duplicate values of file name. I want to delete the duplicate value from both the table which are repeating in both the table.

Thanks for your time.

make the field unique http://stackoverflow.com/questions/15255304/how-add-unique-key-to-existing-table-with-non-uniques-rows — Haim Evgi, Oct 14 '13 at 11:23

score 0 · Answer 1 · answered Oct 14 '13 at 11:25

0

You can add an index on the field and use ignore to drop the duplicate rows

ALTER IGNORE TABLE yourencypt_table ADD UNIQUE INDEX enc_idx (FILE_ENCRYPTED);
ALTER IGNORE TABLE yourdecypt_table ADD UNIQUE INDEX dec_idx (FILE_DECRYPTED);

answered Oct 14 '13 at 11:25

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Oct 14 '13 at 11:25

If I understand you correctly,

You have two tables with columns FILE_ENCRYPTED, FILE_DECRYPTED respectively.

You want to find values which are common to both databases.

I'd suggest that you

SELECT * FROM table1 JOIN table1 ON table1.FILE_ENCRYPTED = table2.FILE_DECRYPTED

This will give you the matching rows.

You can then use DELETE to remove the records

Then, you would add a UNIQUE INDEX on the fields:

ALTER IGNORE TABLE table1 ADD UNIQUE INDEX enc_idx (FILE_ENCRYPTED)
ALTER IGNORE TABLE table2 ADD UNIQUE INDEX dec_idx (FILE_DECRYPTED)

How do I remove duplicate data from two different table in Mysql

2 Answers2