CODEWITHSUNDEEP
phpmysql
user7573053
user7573053

Reputation: 19

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

Consider I have a table structure like this, 

id    Email
-----------
1    [email protected]
2    [email protected]
3    [email protected]
4    [email protected]
5    [email protected]

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?

Upvotes: 0

Views: 465

Answers (2)

Meera Tank
Meera Tank

Reputation: 719

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.

Upvotes: 1

haMzox
haMzox

Reputation: 2109

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

Upvotes: 0

Related Questions