List:General Discussion« Previous MessageNext Message »
From:Johan Höök Date:August 24 2005 7:16am
Subject:Re: INSERT IGNORE Doesn't Seem To Work
View as plain text  
Hi Hal,
in order to get INSERT IGNORE to work as you want it you must violate
a unique index somehow, i.e. you must have a unique index on Name,Value
or both and then you would get a quiet ignore of that violation.
The IGNORE keyword doesn't make the INSERT as such different, it just
affects the errorhandling of a UNIQUE KEY violation.

Regards,
	/Johan

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
> 

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