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?
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
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.