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.

Thanks
bmarshallbriAsked:
Who is Participating?
 
Raynard7Connect With a Mentor Commented:
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
0
 
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)
0
 
bmarshallbriAuthor Commented:
Great, thanks. That works nicely.

Much appreciated.
0
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.

All Courses

From novice to tech pro — start learning today.