Re: INSERT IGNORE Doesn't Seem To Work
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
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]
Re: INSERT IGNORE Doesn't Seem To Work
Hi Hal, in order to get INSERT IGNORE to work as you want it you must violate a unique index somehow, i.e. you must have a unique index on Name,Value or both and then you would get a quiet ignore of that violation. The IGNORE keyword doesn't make the INSERT as such different, it just affects the errorhandling of a UNIQUE KEY violation. Regards, /Johan 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
The insert will only be bounced where you specify the columns as unique. Thus you need either separate UNIQUE indexes on Name and Value, if you want them to be individually unique, or a single joint UNIQUE index if you want them to be jointly unique but separately duplicable. The INSERT command only checks columns that is instructed are to be unique. The purpose of the IGNORE modifier is simply to ignore the error produced when a duplicate occurs. Alec Hal Vaughan [EMAIL PROTECTED] 24/08/2005 07:47 Please respond to [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject INSERT IGNORE Doesn't Seem To Work 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
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
