Mysql: Insert or Update in One SQL Statement Mysql: Insert or Update in One SQL Statement

I must say, I really wish I knew about this technique years ago – before I started using frameworks to do a lot of my development.  The effort that was wasted in building separate code and separate logic to deal with inserts vs updates, when all of this time, there is a nice and simple feature built-in to Mysql that does this for us…


 The process is quite straight forward, when I run a standard INSERT statement with Mysql, I can provide additional properties for dealing with DUPLICATE KEY, e.g. the record already exists.

Here is an example: 


INSERT INTO `table` (`name`, `created`, `modified`)
VALUES ('Jamie', NOW(), NOW())
ON DUPLICATE KEY
UPDATE `name` = 'Jamie', `modified` = NOW()

Notice how in the UPDATE statement I'm only updating the name and modified date.

Another nice example can be used for keeping track of the number of times a tag is used.  Imagine you have a table with a list of tags and their associated count.  You can leverage the above functionality to increase the count each time the tag is used again: 


INSERT INTO `tags` (`tag`, `count`, `created`, `modified`)
VALUES ('mysql', 1, NOW(), NOW())
ON DUPLICATE KEY
UPDATE `count` = `count` + 1, `modified` = NOW()

That's it!  No more need to right functionality like: 


if ($id == 0) {
// do insert
} else {
// do update
}

For my information, visit Mysql Documentation.

Published on Oct 3, 2012

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | insert | update | duplicate

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Jamie Munro
Jamie Munro

Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js, Knockout.js: Building Dynamic Client-Side Web Applications, 20 Recipes for Programming MVC 3, and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years.

Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.

Front-End

Server-Side

Database