CODEWITHSUNDEEP
mysqlduplicatesduplicate-data
Gareth Daine
Gareth Daine

Reputation: 4196

Remove Duplicate Values from Field in MySQL Table

I'm after some help putting together a query to remove duplicate data in a specific field in one of my tables.

I have a table called contacts that I need to import in to another system. The new system requires that the email field be unique. I need a query that will allow me to search the email field and remove any duplicate data or set it to "".

I don't want to remove the rows, just the duplicate of the email. So if there are two records contain the email [email protected], then I want to keep the first reference while removing the second.

Seems like this should be a simple thing to do but I'm struggling working our how to achieve it. Thanks.

Upvotes: 1

Views: 1401

Answers (2)

Illuminati
Illuminati

Reputation: 565

UPDATE CONTACTS SET b.email = ""
FROM CONTACTS a, CONTACTS b
WHERE a.EMAIL = b.EMAIL AND a.ID>b.ID

Another Possible solution

Reference : 

http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005

Upvotes: 0

Neil Hampton
Neil Hampton

Reputation: 1883

You need to use a query similar to the following:

UPDATE CONTACTS A, CONTACTS B
  SET B.EMAIL=NULL
WHERE A.EMAIL=B.EMAIL
  AND A.KEY_FIELD>B.KEY_FIELD

Use the field reference to determine which is removed.

Upvotes: 3

Related Questions