Solved

A Crazy Case of Inserting or Updating MySQL

Posted on 2014-03-26
11
487 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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 109

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Varible Table 3 27
Query Syntax 17 37
How do I fix this UPDATE error? 7 24
How to check if a session is disconnected in php 9 17
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.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
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…

825 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