List:General Discussion« Previous MessageNext Message »
From:Adaikalavan Ramasamy Date:July 26 2004 4:47pm
Subject:Re: INSERT if record NOT EXISTS
View as plain text  
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong ?

mysql> desc tb;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| myID      | int(11)     |      | PRI | NULL    | auto_increment |
| firstname | varchar(10) | YES  | MUL | NULL    |                |
| lastname  | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
DUPLICATE KEY UPDATE lastname = lastname;
ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
UPDATE lastname = lastname' at line 1

Alternatively, I am looking for 'try' equivalent in perl, so that if the
insert is duplicate, the rest of the script is still run. Thank you.

Regards, Adai.


On Mon, 2004-07-26 at 17:20, Michael Dykman wrote:
> 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.
> > 
> > 
> > 
> > 
> > 

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