troubleshooting Question

MySQL Ignore Duplicate Insert on MULTIPLE Columns

Avatar of MacGyverSolutions
MacGyverSolutionsFlag for United States of America asked on
PHPMySQL ServerSQL
6 Comments1 Solution1156 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros