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";
> 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?
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?