insert into problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
playwright
Forum Newbie
Posts: 20
Joined: Wed Jun 02, 2010 6:11 pm

insert into problem

Post 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???
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: insert into problem

Post by mikosiko »

use an UNIQUE index and INSERT IGNORE

http://dev.mysql.com/doc/refman/5.1/en/insert.html
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: insert into problem

Post 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.
playwright
Forum Newbie
Posts: 20
Joined: Wed Jun 02, 2010 6:11 pm

Re: insert into problem

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: insert into problem

Post by califdon »

That's why I asked. Go back and read my previous post for the answer to your question.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: insert into problem

Post 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.
Post Reply