Solved

A Crazy Case of Inserting or Updating MySQL

Posted on 2014-03-26
11
477 Views
Last Modified: 2014-04-01
I have a table that looks like this:

ID     name      emp_id
1      Sally         456
2      unknown  457
3      Kate         null
4      unknown  890
5      James      null

Open in new window


I know it's crazy, but it's what I'm dealing with according to business case.

I need to sometimes update the name, sometimes the emp_id. PLUS, I need to return back the updated or inserted ID.

If it were MSSQL, I'd do this:

IF EXISTS (SELECT 1 FROM tblEmp WHERE name = 'Kate')  
BEGIN
UPDATE tblEmp SET emp_id = 767 WHERE name = 'Kate'
ELSE
INSERT INTO tblEmp Set emp_id = 767, name = 'Kate'
END

Open in new window


How can I do this elegantly using mySQL?

Thank you!
0
Comment
Question by:EffinGood
  • 5
  • 5
11 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39957770
What are you using to connect? PDO, MySQLi....?
They all have methods to get the last id.
0
 

Author Comment

by:EffinGood
ID: 39957772
Hi Gary,

I'm using MySQLi

Thank you
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 39957795
So something just like this, change the object to fit your naming

$mysqli->query($query);
echo $mysqli->insert_id;

Oh wait, you need the sql as well - which column is unique? Name?
Syntax is along these lines

INSERT INTO table SET col1=val1, col2=val2 ON DUPLICATE KEY UPDATE col1=val1, col2=val2
0
 

Author Comment

by:EffinGood
ID: 39957872
Hi Gary,

Yes this is the issue I'm having: both name and emp_id are unique. How do I use ON DUPLICATE KEY UPDATE in this case?
0
 
LVL 58

Expert Comment

by:Gary
ID: 39957876
The ON DUPLICATE KEY UPDATE wouldn't matter if two columns are unique, if inserting a new record would create a conflict on either column then it would update the row instead.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:EffinGood
ID: 39957889
OK, so I go back to my initial question, how do I do this in a nice, elegant way?

I appreciate your help. Thank you!
0
 
LVL 58

Expert Comment

by:Gary
ID: 39957893
What's wrong with #39957795, if need be it will insert or else update.
0
 

Author Comment

by:EffinGood
ID: 39957899
OH, OK, I see. I think one of the problems I'm having is truly understanding on duplicate. As a MSSQL dev, it's new to me and I'm having trouble finding good examples of it's use.

Thanks, m8. I'll go give it a go and let you know if I run into any other snags and accept solution.
0
 
LVL 58

Expert Comment

by:Gary
ID: 39958028
It's kinda the same as the if...else just a more shorthand version.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39958563
I have a hard time getting my head around a data base table that has a human name marked UNIQUE, but that aside, you might be able to make something useful with this clause, assuming that you have a UNIQUE key across both name and emp_id:
https://dev.mysql.com/doc/refman/5.7/en/replace.html
0
 

Author Closing Comment

by:EffinGood
ID: 39970374
Thank you! Sorry for the delay.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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.
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 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

22 Experts available now in Live!

Get 1:1 Help Now