List:General Discussion« Previous MessageNext Message »
From:Adaikalavan Ramasamy Date:July 26 2004 3:31pm
Subject:Re: INSERT if record NOT EXISTS
View as plain text  
Thanks Alec ! This works wonderfully.

But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.

This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');


On Mon, 2004-07-26 at 16:20, Alec.Cawley@stripped wrote:
> 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