Hi Guys,

I have a table of contributors like so:
contribid/email/name/surname/url/created/displayname

contribid auto increments.

I want to add a new contributor to the table if his email address doesn't already exist in the table. I thought the following would work but I get a syntax error:

INSERT INTO contributors(email, name, surname,url, displayname) VALUES('someone@hotmail.com', 'Joe', 'Bloggs','www,somewhere.com','Joey') where not exists (email='someone@hotmail.com')

Any help would be appreciated.
Thanks,
Tim.

    I usually handle that by trying to do the insert, and if the query fails, check to see if it's a duplicate key error, and if so, display an appropriate error. Or, if it's not really an error, you could add an ON DUPLICATE KEY UPDATE clause to the insert, and not have it actually change anything, which would then just silently do nothing if the email is already in there.

      I usually run a query first to make sure some conflicting record doesn't already exist. For NogDog's approach to work, you have to define a UNIQUE index on all the columns that must not be duplicated.

        NogDog's suggestion is better as it is likely to be more efficient, and more importantly, it guarantees that you will not have duplicates where uniqueness is required. The problem with checking separately before inserting is that it can run into a race condition where between the time of check and the time of use, a record was inserted that would make the record to be inserted a duplicate.

          Write a Reply...