MySQL Ignore Duplicate Insert on MULTIPLE Columns

MacGyverSolutions
MacGyverSolutions used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi MacGyver,

What about a unique (composite) index across First, Last, birthdate, and address?


Kent
Aaron TomoskyDirector of Solutions Consulting

Commented:
Insert ignore only works on a unique column or unique column group. Put a unique index on that column group and it will work.
You can achieve by running following command

CREATE UNIQUE INDEX unique_rec ON table_name(first_name, last_name, birth_date, address);

Please note whenever you fire a query and the record is duplicate you would get a mysql error which can be caught as shown in the sample code.

You might want to change the code as per your needs.
$sql=  'INSERT INTO `table` (`first_name`,`last_name`,`birth_date`, `address`) ';
$sql.= "VALUES ('$fname', '$lname', '$dob','$address')";
$result= @mysql_query($sql);
if ( mysql_errno() == '1062' )
{
  die("I'm sorry, that record is duplicate!.");
} else {
  //This is to capture any other mysql error
  die('Error with query:<br />' .mysql_error());
}

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

p.s. If you do not want to capture the error then you can supress it using "INSERT IGNORE" MySQL directive.

Author

Commented:
Ah - I thought a composite index would effectively set all columns to be unique, not unique as a group. I will try this out!

Author

Commented:
Not only did this work, the inserts were fast! Thanks all!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial