DELETE duplicate rows from a table and its corresponding data in another table in mysql

Question

Consider I have a table structure like this,

id    Email
-----------
1    abc@gmail.com
2    abc1@gmail.com
3    abc@gmail.com
4    abc@gmail.com
5    abc@gmail.com

And another table

id   userId     name
---------------------
1    1          A
2    2          B
3    3          C
4    4          D
5    5          E

Now if I want to remove duplicates from table 1, i.e. 2, 3, 4, 5 should be deleted from table1 and its corresponding data in table2.

How would I do this?
Is this possible?

use foreign key with combination of on delete cascade. Please go through this [link](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) — Rahul, Apr 07 '17 at 04:55
This might help you http://stackoverflow.com/a/15548724/2681855 — ManiMuthuPandi, Apr 07 '17 at 04:58

score 1 · Answer 1 · answered Apr 07 '17 at 05:40

1

first try to delete duplicate row from user table by keeping the one using below code :

DELETE FROM `user` WHERE `id` NOT IN (select * from ( SELECT MIN(`id`) FROM `user` GROUP BY `email` ) as t)

secondly try to delete other dependent rows from another table by following :

DELETE FROM `user_data` WHERE `u_id` NOT IN (select * from ( SELECT MIN(`id`) FROM `user` GROUP BY `email` ) as t)

hope this will work for you.

answered Apr 07 '17 at 05:40

Meera Tank

score 0 · Answer 2 · answered Apr 07 '17 at 04:58

0

In a DML way, try the following query with your table (Edit it according to your tables). NOTE: Delete can be rolled back.

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

answered Apr 07 '17 at 04:58

haMzox

I want to fine duplicates and then delete them. – user7573053 Apr 07 '17 at 05:06
The above query is going to help you for deleting. For finding duplicates try: SELECT email, COUNT( * ) FROM table_name GROUP BY email HAVING COUNT( * ) > 1 For more check: http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table – haMzox Apr 07 '17 at 05:11
That i have already tried, requirement is if i can use only one query o do that?? – user7573053 Apr 07 '17 at 05:16
I think my answer was enough. DELETE query is the only one way query. – haMzox Apr 07 '17 at 05:19

2 Answers2