Remove Duplicate Values from Field in MySQL Table

Question

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.

Do you have a primary key on the table?

Neil Hampton
– Neil Hampton

2013-08-06 17:55:52 +00:00
Commented Aug 6, 2013 at 17:55 — Neil Hampton
– Neil Hampton, Commented Aug 6, 2013 at 17:55

Neil Hampton · Accepted Answer · 2013-08-06 17:57:49Z

3

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.

answered Aug 6, 2013 at 17:57

Neil Hampton

1,88313 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gareth Daine Over a year ago

Hi, thanks. What's the A and B bit for? I'm pretty green with SQL.

Neil Hampton Over a year ago

The A and B are aliases for the table - it allows you to join a table with itself and thus look for the duplicates.

Gareth Daine Over a year ago

Oh great, and finally what is the KEY_FIELD part?

Neil Hampton Over a year ago

There must be another column in your contacts table that determines which is the first email reference (for example, a row ID or maybe a timestamp for the insertion). KEY_FIELD is my place holder for this column - you need to use whatever the suitable column in your table is.

Gareth Daine Over a year ago

well I have a unique id column and I tried with that but it said it didn't exist?

|

Illuminati · Accepted Answer · 2013-08-06 20:01:20Z

0

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

answered Aug 6, 2013 at 20:01

Illuminati

5652 gold badges10 silver badges36 bronze badges

Collectives™ on Stack Overflow

Remove Duplicate Values from Field in MySQL Table

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related