List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 24 2005 5:00pm
Subject:Re: INSERT IGNORE Doesn't Seem To Work
View as plain text  
Hal Vaughan <hal@stripped> wrote on 08/24/2005 12:41:36 PM:

> On Wednesday 24 August 2005 02:47 am, Hal Vaughan wrote:
> > I may have a misunderstanding of this, but as I have been told, if I 
have a
> > table with 3 columns, Idx (an Index column, unique, auto-increment), 
Name,
> > Value (both varchar), and I try a command like this:
> >
> > INSERT IGNORE INTO myTable SET Name = "Variable1", Value = "100";
> > or
> > INSERT IGNORE INTO myTable (Name, Value) VALUES("Variable1", "100");
> >
> > AND I already have a row with the matching Name and Value columns 
matching
> > in value, that MySQL will detect that and not insert the redundant 
values. 
> > I've also tried this without a unique, auto-increment column, just 
trying
> > to insert by specifying values for all 3 columns that already match an
> > existing row, and it still doesn't work.
> >
> > I thought the IGNORE keyword was intended to be used to prevent 
duplicating
> > values, and that it matched the values in the INSERT statement (even 
if not
> > all columns in the table were given a value) against the ones in the 
table
> > and would NOT INSERT the row if it matched.
> >
> > I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not
> > through downloading).
> >
> > So this brings up a few questions: 1) Am I doing something wrong?  2) 
Is
> > this what INSERT IGNORE is supposed to do -- if not, what does it do?, 
and
> > 3) If this isn't what INSERT IGNORE does, how can I do what I 
*thought* it
> > did -- insert only if the value doesn't already exist?
> >
> > Thanks!
> >
> > Hal
> 
> Okay, so INSERT IGNORE only works if I am avoiding duplicate keys.  Is 
there 
> any way to use INSERT the way I thought INSERT IGNORE worked -- in other 

> words is there any keyword for the INSERT command to keep it from 
duplicating 
> rows if there isn't a key?
> 
> Hal
> 

Not really. You have to define the table in such a way that some kind of 
duplicated data is "wrong" before the SQL engine can guard against them. 
Exactly what form of duplication you don't want is entirely up to you and 
your needs. You tell the SQL engine what kind of duplication to reject by 
either defining your PRIMARY KEY or a UNIQUE key or some combination of 
PRIMARY and UNIQUE keys in such a way to dissalow the duplication you want 
to avoid. Otherwise you will need to search for duplicates in your 
application BEFORE you build your INSERT statement so that you just do not 
execute any INSERT statements that would create duplicated information.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
INSERT IGNORE Doesn't Seem To WorkHal Vaughan24 Aug
  • Re: INSERT IGNORE Doesn't Seem To WorkJohan Höök24 Aug
  • Re: INSERT IGNORE Doesn't Seem To WorkAlec.Cawley24 Aug
  • Re: INSERT IGNORE Doesn't Seem To WorkHal Vaughan24 Aug
    • Re: INSERT IGNORE Doesn't Seem To WorkSGreen24 Aug
    • Re: INSERT IGNORE Doesn't Seem To WorkAlec.Cawley25 Aug
      • Re: INSERT IGNORE Doesn't Seem To WorkHal Vaughan26 Aug
        • Re: INSERT IGNORE Doesn't Seem To WorkPeter Brawley26 Aug