INSERT or ignore?

Spudhead · #1 (**permalink**) 02-11-09, 08:07

I've got a table for a mailing list web application, containing a column for the email address and a column for the List ID.

I need to run through an uploaded CSV file, adding new records for new email addresses for the selected list ID.

I can only see a way to do this using two queries, one to see if there are any existing records and another to do the actual insert (if there aren't):

SELECT id FROM tblContacts WHERE listID = 123 and email = 'me@web.com'

INSERT INTO tblContacts (listID, email) VALUES (123, 'me@web.com')

Is there a way to do it with just the one?

mike_bike_kite · #2 (**permalink**) 02-11-09, 08:16

Would this work ? :

Code:

INSERT INTO tblContacts (listID, email) 
select csv.listID,  csv.email
from   csvTable csv
where  not exists( 
         select 1
         from   tblContacts c
         where  c.listID = csv.listID
                and c.email = csv.email )

Spudhead · #3 (**permalink**) 02-12-09, 04:04

I hadn't thought of doing it like that - my application currently reads CSV contents into an array and loops through it - but it's certainly an option; just dump the CSV into a temporary table and use that. Thanks

r937 · #4 (**permalink**) 02-12-09, 04:54

yes, load the csv into a table

then run this --

Code:

INSERT IGNORE
  INTO Contacts
SELECT listID, email
  FROM CSVtable

Quote:

Originally Posted by da 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.

just what you wanted

Spudhead · #5 (**permalink**) 02-12-09, 05:24

Aah... but that would depend on my existing tblContacts having the email address and list ID fields as the primary key, instead of the autoincrement integer field it's got already, right?

Me and my autoincrementing ID's