Solved

Insert if Exists else return id

Posted on 2006-07-06
3
295 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

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.

Question has a verified solution.

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

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://…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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