List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:July 26 2004 3:28pm
Subject:Re: INSERT if record NOT EXISTS
View as plain text  
Your model is flawed.
My son and I have the same first and last names.
Therefore, we could not be users on your system.

Adaikalavan Ramasamy wrote:

>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' );
>
Just do a normal insert, and check to see if it failed.
Then deal with it.

You could also use INSERT IGNORE.

>
>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