From: Michael Stassen
Date: May 5 2004 3:35pm
Subject: Re: if exists
List-Archive: http://lists.mysql.com/mysql/164891
Message-Id: <409909D8.7070708@verizon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I think this is half the answer (though you've implied the other half).

First, you prevent mysql from accepting duplicate rows by defining a PRIMARY 
KEY or UNIQUE INDEX on whatever column or columns determine uniqueness.  See 
<http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html> for the details. 
Then, you can INSERT without fear of creating duplicate rows.  Adding IGNORE 
tells mysql not to bother you with an error message if your INSERT is a 
duplicate.

If it is possible that your current info supersedes what is already in the 
table, you might want to take a look at the manual page for REPLACE,

<http://dev.mysql.com/doc/mysql/en/REPLACE.html>,

or, if you use mysql 4.1.x, INSERT...ON DUPLICATE KEY UPDATE...

<http://dev.mysql.com/doc/mysql/en/INSERT.html>.

Michael

emierzwa@stripped wrote:

> Silently ignore duplicate index inserts...
> 
> insert IGNORE into PERSON ( NAME ) values ( 'Test')
> 
> Ed
> -----Original Message-----
> From: Gerhard Gaupp [mailto:g.gaupp@stripped] 
> Hello
> 
> I'm looking for something like
> 
> if not exists( select 1 from PERSON where NAME = 'Test' ) then
>    insert into PERSON ( NAME ) values ( 'Test');
> end if;
> 
> I did it with Sybase Database.
> I need to get Data from Excel and have to be shure that there is no data
> more than once in the table.
> So I have to generate a script with test, if this data is in teh table
> yet, or not, before inserting.
> 
> Did You Understand? Sorry for my English
> 
> Greetings from Germany
> 
> Gerhard
>