Delete duplicate column value from mysql

Question

I have mysql table named emails where multiple column like

"first_name","last_name","email","contact"

I have huge data in this table with many multiple email id. I want to make this data as unique where not any single email in other row.

Kindly share how to write SQL query for it

Thanks Paras

You can use one table with different aliases look here http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — user1954544, Apr 22 '14 at 09:22
Also do the multiple email ID's have different first/last names or contact values? — Matt, Apr 22 '14 at 09:23

score 1 · Answer 1 · answered Apr 22 '14 at 09:24

1

First you may need to find the list of duplicate emails using the following query.

select email, COUNT(*) as cnt  
FROM emails   
group by email  
order by cnt desc

The query will give emails and the count of repetition. Then based on which record is valid, take deletion steps.

answered Apr 22 '14 at 09:24

ray

457
3
9

Thanks for helping I want to delete duplicate entry from emails table where email column has duplicate value. – Paras Parmar Apr 22 '14 at 09:33
yes i get list of duplicate entry. using SELECT query you given – Paras Parmar Apr 22 '14 at 09:56

Matt · Answer 2 · 2014-04-22T10:10:40.677

1

Use this

SELECT * FROM emails
WHERE email IN (
    SELECT DISTINCT email 
    FROM emails
    WHERE email IN (
        SELECT COUNT(*) AS [Number],
               email,
               first_name,
               last_name,
               contact
        FROM emails   
        GROUP BY email, first_name, last_name, contact
                   )
               )

Then if it returns the correct results use this

DELETE FROM emails
WHERE email IN (
    SELECT DISTINCT * 
    FROM emails
    WHERE email IN (
        SELECT COUNT(*) AS [Number],
               email,
               first_name,
               last_name,
               contact
        FROM emails   
        GROUP BY email, first_name, last_name, contact
                   )
               )

edited Apr 22 '14 at 10:10

answered Apr 22 '14 at 09:35

Matt

14,906
27
99
149

I have done select query and get error as bellow #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM emails WHERE email IN ( SELECT DISTINCT * FROM emails WHERE em' at line 1 – Paras Parmar Apr 22 '14 at 09:43
@ParasParmar Needs to be SELECT * FROM, update my answer – Matt Apr 22 '14 at 09:51
SELECT * FROM emails WHERE email IN ( SELECT DISTINCT * FROM emails WHERE email IN ( SELECT COUNT(*) AS [Number] email, first_name, last_name, contact FROM emails GROUP BY email, first_name, last_name, contact ) ) this is gives error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[1] email FROM emails GROUP BY email ' at line 6 – Paras Parmar Apr 22 '14 at 09:55
@ParasParmar Have a go now – Matt Apr 22 '14 at 10:10

Delete duplicate column value from mysql

2 Answers2