Hal,
/>*IF* INSERT IGNORE worked .../
INSERT IGNORE _does_ work exactly as documented in the manual: "If you
specify the |IGNORE| keyword in an |INSERT| statement, errors that occur
while executing the statement are treated as warnings instead. For
example, without |IGNORE|, a row that duplicates an existing |UNIQUE|
index or |PRIMARY KEY| value in the table causes a duplicate-key error
and the statement is aborted. With |IGNORE|, the error is ignored and
the row is not inserted. Data conversions that would trigger errors
abort the statement if |IGNORE| is not specified. With |IGNORE|, invalid
values are adjusted to the closest value values and inserted; warnings
are produced but the statement does not abort."
(http://dev.mysql.com/doc/mysql/en/insert.html)
/>, it was easy for me to simply add "IGNORE " to a query
>string (this is all in Perl) for tables where I did not want dupes. /
In relational databases, the usual method of preventing duplicate values
is via PRIMARY or UNIQUE indexes. Absent such indexes, you need
application code to prevent dupes.
PB
-----
Hal Vaughan wrote:
>On Thursday 25 August 2005 04:44 am, Alec.Cawley@stripped wrote:
>
>
>>Hal Vaughan <hal@stripped> wrote on 24/08/2005 17:41:36:
>>
>>#>
>>
>>
>>
>>>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?
>>>
>>>
>>I don't think so. But may I inquire why you do not want to have a key?
>>What you are saying is "How can I do a job without using the tool designed
>>for the job?". If there is no key, in order to do what you want, MySQL
>>would have to do a linear search through the table in order to check for
>>duplicates - the kind of lengthy operation it is designed to avoid
>>whenever possible. The key is a necessary part of the effect you want to
>>achieve.
>>
>> Alec
>>
>>
>
>I have some routines for entering large amounts of data into different tables.
>*IF* INSERT IGNORE worked, it was easy for me to simply add "IGNORE " to a
>query string (this is all in Perl) for tables where I did not want dupes. I
>also have a number of tables where there are reasons for allowing multiple
>entries. There are also some tables where items from one source must not be
>duplicated, where entries from another source should be, since they are
>counted later.
>
>Hal
>
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.15/81 - Release Date: 8/24/2005
