Most efficient way of checking for duplicate entries
Posted: Mon Feb 22, 2010 12:17 pm
Hi there,
I have some queries where I am adding telephone numbers and some related info into a MySQL table.
I have made the telephone_num column the primary key because I do not want duplicate information relating to a telephone number.
I was trying to work out the best way to add telephone numbers to the table. I thought that as the telephone_num column was the primary key, then if just tried to add a record with the same telephone number then it simply wouldn't add it. This is fine as far as I'm concerned, I just wouldn't handle the error message.
The issue I'm having is that when I try to add more than one telephone numbers that have even one duplicate number, then the whole query fails and none of the numbers get added.
I would like to have a query that ignores duplicate records, but adds non-duplicate records.
The only way I can think of is to do something like:
and look for duplicates using php before I try to insert anything into the MySQL database.
I'm happy to do this, but if the database got to be large then I'm thinking it might just add an unnecessary step which would make the program slower.
So my question is, is there a way of inserting multiple rows into a table that will ignore any duplicate records while adding the unique ones?
Many thanks in advance
Joe
I have some queries where I am adding telephone numbers and some related info into a MySQL table.
I have made the telephone_num column the primary key because I do not want duplicate information relating to a telephone number.
I was trying to work out the best way to add telephone numbers to the table. I thought that as the telephone_num column was the primary key, then if just tried to add a record with the same telephone number then it simply wouldn't add it. This is fine as far as I'm concerned, I just wouldn't handle the error message.
The issue I'm having is that when I try to add more than one telephone numbers that have even one duplicate number, then the whole query fails and none of the numbers get added.
I would like to have a query that ignores duplicate records, but adds non-duplicate records.
The only way I can think of is to do something like:
Code: Select all
SELECT telephone_num FROM my_table;I'm happy to do this, but if the database got to be large then I'm thinking it might just add an unnecessary step which would make the program slower.
So my question is, is there a way of inserting multiple rows into a table that will ignore any duplicate records while adding the unique ones?
Many thanks in advance
Joe