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.

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.