Solved

Insert if Exists else return id

Posted on 2006-07-06
3
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 17055666
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
ID: 17055685
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
ID: 17059895
Great, thanks. That works nicely.

Much appreciated.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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