Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

A Crazy Case of Inserting or Updating MySQL

Posted on 2014-03-26
11
Medium Priority
?
503 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
[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
  • 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 2000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 

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 111

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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