List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 26 2005 4:08am
Subject:Re: INSERT IGNORE Doesn't Seem To Work
View as plain text  
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
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