Page 1 of 1

insert into problem

Posted: Wed Oct 06, 2010 10:32 am
by playwright
Hello.I have created a table with some rows. Then, i want to add more rows in the table using insert into. However, i need to check if any of the rows that i add is identical to a row that already exists, so as to avoid adding identical rows in the same table. I dont want to create a new table. I tried using distinct or unique but i dont think that they are approriate. Any ideas???

Re: insert into problem

Posted: Wed Oct 06, 2010 2:04 pm
by mikosiko
use an UNIQUE index and INSERT IGNORE

http://dev.mysql.com/doc/refman/5.1/en/insert.html

Re: insert into problem

Posted: Wed Oct 06, 2010 3:29 pm
by califdon
playwright wrote:Hello.I have created a table with some rows. Then, i want to add more rows in the table using insert into. However, i need to check if any of the rows that i add is identical to a row that already exists, so as to avoid adding identical rows in the same table. I dont want to create a new table. I tried using distinct or unique but i dont think that they are approriate. Any ideas???
How are you defining "identical"? You mean that EVERY column contains identical data? Is there a primary key? Is the primary key an auto-increment field?

In general, you need to perform a query that searches for a row in the table that has the same data as is proposed to be added. If it finds such a record, don't insert a new row. If it doesn't find such a record, insert a new row.

Re: insert into problem

Posted: Mon Oct 11, 2010 6:01 am
by playwright
Unfortunately, i realised that rows aren't exactly identical.
I have entries that are like these:
First column:user_id (primary key) Second column:book_name
(1,'Things Fall Apart')
(2,'Things Fall Apart')
(3,'The Famished Road')
I dont want to insert entries that have the same book name for example. i cant do it by using insert ignore since the two rows are not identical.

Re: insert into problem

Posted: Mon Oct 11, 2010 11:36 am
by califdon
That's why I asked. Go back and read my previous post for the answer to your question.

Re: insert into problem

Posted: Mon Oct 11, 2010 11:44 am
by John Cartwright
playwright wrote: I dont want to insert entries that have the same book name for example. i cant do it by using insert ignore since the two rows are not identical.
So if you are only defining unique rows by a single column (ignore the primary key in this case), then set that column as unique and use the insert ignore statement.