List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:May 5 2004 3:35pm
Subject:Re: if exists
View as plain text  
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
> 

Thread
Scheduled queriesJuan Riera18 Mar
  • Patch: Re: Unknown errorMichael Widenius8 Nov
Re: Scheduled queriesEd Carp18 Mar
RE: if existsemierzwa5 May
  • Re: if existsMichael Stassen5 May