INSERT DISTINCT?
Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Create a unique index on each column that you don't want to be duplicated. create UNIQUE index table_u1 on table(some_column) --- John Mistler [EMAIL PROTECTED] wrote: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- 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 DISTINCT?
This is certainly the first step. Also, if you want to insert only those rows which are not already present, you can use something akin to: INSERT INTO table1 SELECT * FROM table2 t2 LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for your data) WHERE t1.XYZ IS NULL (obviously put in the appropriate column names etc for your data structure!) Cheers, Matt -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 22:22 To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- 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 DISTINCT?
Try insert ignore. Check the manual page for insert syntax. On Wednesday, July 7, 2004, at 05:22 PM, Matt Chatterley wrote: This is certainly the first step. Also, if you want to insert only those rows which are not already present, you can use something akin to: INSERT INTO table1 SELECT * FROM table2 t2 LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for your data) WHERE t1.XYZ IS NULL (obviously put in the appropriate column names etc for your data structure!) Cheers, Matt -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 22:22 To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT DISTINCT?
John, As far as I'm aware there is no problem creating a multi-column unique index on fields that have already been indexed seperately. The multi column index will be used in situations where you are trying to find unique _combinations_ of the two fields and the individual indexes in situations where you are trying to find instances of one OR the other. If you are doing a number of inserts or multi-row inserts you may want to use the IGNORE parameter. This will allow the INSERT command to complete and return successfully, simply skipping the rows where dupes are found, otherwise mysql is going to generate a duplicate key error when duplicates are found. Hope this clarifies some things for you. Lachlan -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Thursday, 8 July 2004 12:27 PM To: [EMAIL PROTECTED] Subject: Re: INSERT DISTINCT? I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- 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 DISTINCT?
John,
Here is an example which should clarify for you:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
Let's say testTable looks like this:
+-+
| a | b |
--|
| AVAL | EVAL |
| FVAL | BVAL |
+-+
You have your unique index over (a,b). The insert statement above will
succeed because there is no entry in testTable where _both_ a and b are as
specified in the INSERT.
The table should then look like:
+-+
| a | b |
--|
| AVAL | EVAL |
| FVAL | BVAL |
| AVAL | BVAL |
+-+
Any of the following INSERT statements would now fail:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
INSERT INTO testTable (a,b) VALUES ('AVAL', 'EVAL');
INSERT INTO testTable (a,b) VALUES ('FVAL', 'BVAL');
Hope this helps,
Regards,
Lachlan
PS. Please remember to reply to the [EMAIL PROTECTED] address rather
than my direct address, this way everyone can keep track of the discussion.
-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 July 2004 1:24 PM
To: Lachlan Mulcahy
Subject: Re: INSERT DISTINCT?
Thanks, Lachlan! One other thing I am wondering: given two columns (a,b)
with a UNIQUE multi-column index over them, will an insert fail if a row
with column a's data already exists, and the data for column b does not? I
am wanting to only prevent inserts when BOTH a b's data already exists.
Thanks,
John
on 7/7/04 7:46 PM, Lachlan Mulcahy at [EMAIL PROTECTED] wrote:
John,
As far as I'm aware there is no problem creating a multi-column unique
index
on fields that have already been indexed seperately. The multi column
index
will be used in situations where you are trying to find unique
_combinations_ of the two fields and the individual indexes in situations
where you are trying to find instances of one OR the other.
If you are doing a number of inserts or multi-row inserts you may want to
use the IGNORE parameter. This will allow the INSERT command to complete
and return successfully, simply skipping the rows where dupes are found,
otherwise mysql is going to generate a duplicate key error when duplicates
are found.
Hope this clarifies some things for you.
Lachlan
-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 July 2004 12:27 PM
To: [EMAIL PROTECTED]
Subject: Re: INSERT DISTINCT?
I am not certain from the documentation whether it is advisable to create
a
unique multi-column index on two columns that are already individually
indexed. The individual indexes I assume I need for when I do a SELECT on
those particular columns. The multi-column one I need for the reasons
discussed below. Any one know?
Thanks,
John
on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote:
Certainly, it's called making a unique index on the field(s) you want to
keep
unique.
Hope that helps.
j- k-
On Wednesday 07 July 2004 12:48 pm, John Mistler said something like:
Is there a way to do an INSERT on a table only if no row already exists
with the same info for one or more of the columns as the row to be
inserted? That is, without using a method outside SQL?
Thanks,
John
--
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 DISTINCT?
At 19:26 -0700 7/7/04, John Mistler wrote: I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns. In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A. You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B). http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Paul, What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix? Tripp --- Paul DuBois [EMAIL PROTECTED] wrote: At 19:26 -0700 7/7/04, John Mistler wrote: I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns. In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A. You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B). http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
From the manual http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html: In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. So, yes, the multi-column index on A,B will serve as the necessary index to make A a foreign key. You can still drop the single column index on A. Michael Emmett Bishop wrote: Paul, What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix? Tripp --- Paul DuBois [EMAIL PROTECTED] wrote: At 19:26 -0700 7/7/04, John Mistler wrote: I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns. In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A. You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B). http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
