Solved

A Crazy Case of Inserting or Updating MySQL

Posted on 2014-03-26
11
494 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 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 

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 110

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

732 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