MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:July 26 2004 3:20pm
Subject:Re: INSERT if record NOT EXISTS
View as plain text  
Adaikalavan Ramasamy <ramasamy@stripped> wrote on 26/07/2004 
16:05:23:

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

Simply create a UNIQUE index on the fields which you with to be unique. 
Add into yoyr table cration the line
        UNIQUE (firstname, lastname),

MySQL will then reject any attempt to make that combination non-unique.

        Alec




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