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