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
>
