Insering data

TheBeginner

Hi,

I insert a lot of data into a database, but sometimes it happens that the row I'm getiing to insert is already there. I don't want double entries, so the solution I have is:

first check if the data is in the table
if not insert it, if yes don't insert.

For that I need two queries all the time, but my question is if it can be done with only one query??

devinemke

no, not that i know of, you have to do 2 queries.

TheBeginner

Damn, I thought maybe I could make it more efficient.

How is it if you already have a table with doublets or more than that. Is there some effecient ways to get rid of the entries?

I will have around 140.000 rows, but if I take away the doublets, tripplets.....then I could probably get down to 10% !!

devinemke

when you say "double entries" what exactly is repeated? is there one particular field that you don't want repeated, for example, two people with the same email?

John Smith john@smith.com
John Smith johnsmith@aol.com

these would not be considered doubles since they are unique emails. what exactly is your criteria for a double?

TheBeginner

No, I can have artist that are in the table twice or more like

Name ID

Elton John 67
Elton John 67
Elton John 67
Jarre 68
Tom 69

Roger_Ramjet

You will have to create an index on that field and make it UNIQUE. That will stop duplicates being added.

To find existing duplicates:

SELECT name_id, count(name_id) FROM artists
GROUP BY name_id
HAVING count(name_id) > 1

will list all artists with more than 1 entry.

If you know how to use nested queries you can use this in a delete query to eliminate the duplicates - but this is complex sql and very easy to get wrong.

Best way, and foolproof. Create a duplicate table, but with the Unique index in place. Copy all the data from the first table into the second. All the duplicates will bounce off. Then rename the tables; and you also have a backup incase anything goes wrong with the second table.

Roger_Ramjet

And, to answer your original question: Yes it can be done in SQL; nested queries again. In this case your insert would be IF NOT EXIST in the nested query. (this SQL syntax is probably foriegn to MySQL so you'll have to see the MySQL manual for it's nested query syntax)

Sxooter

If you're using MySQL you may want to look here:

http://dev.mysql.com/doc/mysql/en/REPLACE.html