Re: INSERT IGNORE Doesn't Seem To Work

[Peter Brawley]

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

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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]