We help IT Professionals succeed at work.
Get Started

MySQL Ignore Duplicate Insert on MULTIPLE Columns

1,146 Views
Last Modified: 2012-05-11
I have an interesting one for the crowd. I need to insert data into a table, but ignore duplicates. This isn't the normal "IGNORE INSERT" question that I'm asking. The ignore must happen on multiple non-unique fields.

For example, let's say we have a table with first name, last name, address, birth date, and phone number. If first name, last name, birth date, and address are the same, then ignore the insert, as we can safely assume this is the same person. However, two different people can have the same first and last name, two different people can have the same birth day, and two different people can have the same address. For this reason, a unique index can not be placed on any column. I want to ignore only the SAME person. The rest of the information such as phone number need to be included in the insert if no duplicate was found.

The reason I need this is because I'm importing data from multiple sources, and some rows from these sources may be the same, so I want the result to be a combination of all sources with no duplicates.

I can do this programmatically (PHP) pre or post-insert, but it involves nested loops and is slow and ugly, so I'd rather not do it this way.

Let me know what you think. Thanks!
Comment
Watch Question
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE