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, [EMAIL PROTECTED] wrote:
> Adaikalavan Ramasamy <[EMAIL PROTECTED]> 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
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
