Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: INSERT or ignore?

    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?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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 )

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all you would need is to add a UNIQUE constraint on (email,listid)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    insert IGNORE
    Is insert IGNORE standard SQL or just proprietary to mysql?
    I seem to remember you saying

    and will he get warnings every time he runs this SQL?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •