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