List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:July 26 2004 4:20pm
Subject:Re: INSERT if record NOT EXISTS
View as plain text  
from http://dev.mysql.com/doc/mysql/en/INSERT.html:
14.1.4 INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

...
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), 
and a row is inserted that would cause a duplicate value in a UNIQUE index 
or PRIMARY KEY, an UPDATE of the old row is performed.
...
<end quote>

there is no IF NOT EXISTS syntax in INSERT, but you could make use of the 
ON DUPLICATE KEY mechanism. Assuming you create a unique index on 
firstname, lastname, your update might read:
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') 
ON DUPLICATE KEY UPDATE lastname = lastname;

which renders the insert neutral.

On Mon, 2004-07-26 at 11:05, 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' );
> 
> In this case, I want no insert because Jack Doe already exists. Can
> anyone help me ?
> 
> Thank you.
> 
> Regards, Adai.
> 
> 
> 
> 
> 
-- 
 - michael dykman
 - michael@stripped

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