Hal Vaughan <[EMAIL PROTECTED]> 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
