0

I am trying to insert contacts into database but does not want to duplicate already existing contact.

Not sure INSERT has WHERE CLAUSE.

Any ideas?

//Insert INTO contact database
$user_id = userid;
$sql_insert = "INSERT into `jt_members_external_contacts`
                            (`j_user_id`,`contact_email`,`firstname`)
               VALUES       ('$user_id','$email','$name')
               WHERE        j_user_id !=$user_id AND contact_email != $email;";
Frank Nwoko
  • 3,816
  • 5
  • 24
  • 33
  • Can you either specify or update your tags to include which SQL database you're using (MySQL, SQL Server '05/'08, etc.)? – Brad Christie Jan 15 '11 at 20:53
  • Simples method is to put a UNIQUE KEY on every field in a record that cannot be duplicated in any other record. – Marc B Jan 15 '11 at 22:18
  • possible duplicate of [How do I update if exists, insert if not (aka upsert or merge) in MySQL?](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) – mmmmmm Jan 18 '13 at 12:23

4 Answers4

1

You can use:

INSERT ... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Ben
  • 16,275
  • 9
  • 45
  • 63
0

Perhaps you want to do an "upsert"? That means that you try to do an INSERT, and if the record already exists, you do an UPDATE instead.

To do that, first use a SELECT to see if the record already exists. If the contact isn't in the database, do an INSERT. If the contact is already in the database, do an UPDATE.

DOK
  • 32,337
  • 7
  • 60
  • 92
0

You could use a stored procedure that checks if the record first exists before doing an insert. If the record exists, the stored procedure would do an update.

The advantage of using a stored procedure instead of a select followed by an update is you only hit the database server once instead of twice.

jamesmortensen
  • 33,636
  • 11
  • 99
  • 120
0

Another option is:

//Insert INTO contact database
$user_id = userid;
$sql_insert = "INSERT into `jt_members_external_contacts`
                            (`j_user_id`,`contact_email`,`firstname`)
               SELECT       '$user_id','$email','$name'
                FROM        `jt_members_external_contacts`
               WHERE        j_user_id !=$user_id AND contact_email != $email;";
Chandu
  • 81,493
  • 19
  • 133
  • 134