List:General Discussion« Previous MessageNext Message »
From:Adaikalavan Ramasamy Date:July 26 2004 3:05pm
Subject:INSERT if record NOT EXISTS
View as plain text  
I am creating a small database that keeps track of users and assigns
them a unique user ID. 

The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :

 CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname       
                VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
  INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe'); 
  INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
  INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
  SELECT * from tb;
+------+-----------+----------+
| myID | firstname | lastname |
+------+-----------+----------+
|    1 | John      | Doe      |
|    2 | Jack      | Doe      |
|    3 | John      | Smith    |
+------+-----------+----------+

I get syntax error with the following :

 INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT  
 EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );

In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?

Thank you.

Regards, Adai.







Thread
INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul
  • Re: INSERT if record NOT EXISTSAlec.Cawley26 Jul
  • Re: INSERT if record NOT EXISTSgerald_clark26 Jul
  • Re: INSERT if record NOT EXISTSMichael Dykman26 Jul
    • Re: INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul
      • Re: INSERT if record NOT EXISTSMarc Slemko26 Jul
  • Re: INSERT if record NOT EXISTSHarald Fuchs27 Jul
Re: INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul
  • Re: INSERT if record NOT EXISTSAlec.Cawley26 Jul
  • Re: INSERT if record NOT EXISTSKeith Ivey26 Jul
    • Re: INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul