Solved

Insert if Exists else return id

Posted on 2006-07-06
3
280 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:bmarshallbri
3 Comments
 
LVL 19

Expert Comment

by:Kim Ryan
Comment Utility
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
 
LVL 35

Accepted Solution

by:
Raynard7 earned 125 total points
Comment Utility
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
 

Author Comment

by:bmarshallbri
Comment Utility
Great, thanks. That works nicely.

Much appreciated.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now