• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Insert if Exists else return id

I'm not really up to speed on all of the option of mysql. I'm developing for 4.1 and I would like to write an insert statement that enforces a unique email address in the email column. If the insert finds a pre-existing match in the email column then I would like it to return my id column value for that record or insert and return the resulting ID after the insert.

Is this possible or do I need to manually query that table first and then insert on the results? I am using InnoDB for my table engines and I am enforcing unique values on that email field.

1 Solution
Kim RyanIT ConsultantCommented:
Try creating a unique index from your email address column. This will prevent insertion of duplicates. You will still need to manually detect when an insert failed because of duplicates, and then retireve the id of ttat record.

create unique index email_index on email ( email_address)
I would do this in two steps;

I would submit the query
select t.id_Column from schemaName.tableName t where t.emailField = 'abcd@wef.com'

If this returns no records then

Insert into schemaName.tableName (emailField) values ('abcd@wef.com')

I agree you need a unique index
bmarshallbriAuthor Commented:
Great, thanks. That works nicely.

Much appreciated.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now