A Crazy Case of Inserting or Updating MySQL

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!
EffinGoodAsked:
Who is Participating?
 
GaryConnect With a Mentor Commented:
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
 
GaryCommented:
What are you using to connect? PDO, MySQLi....?
They all have methods to get the last id.
0
 
EffinGoodAuthor Commented:
Hi Gary,

I'm using MySQLi

Thank you
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
EffinGoodAuthor Commented:
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
 
GaryCommented:
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
 
EffinGoodAuthor Commented:
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
 
GaryCommented:
What's wrong with #39957795, if need be it will insert or else update.
0
 
EffinGoodAuthor Commented:
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
 
GaryCommented:
It's kinda the same as the if...else just a more shorthand version.
0
 
Ray PaseurCommented:
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
 
EffinGoodAuthor Commented:
Thank you! Sorry for the delay.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.