Insert-Update, Merge statement in MySQL

In this post, I discuss related to merge statement for Insert-Update of MySQL.
For all the database developer merge statement is very popular to handle insert, update and delete into a single statement.

Microsoft SQL Server provides merge statement in which you can manage Insert, update and delete.

But unfortunately, MySQL server has no any default Merge Statement. It has  similar alternative option to merge Insert and Update.

You can use INSERT ON DUPLICATE KEY UPDATE in MySQL.

Let’s first create sample table and data.

Use INSERT ON DUPLICATE KEY UPDATE:

In above, code you can see EmpNumber is Primary Key and I am trying to insert same EmpNumber=2 with different EmpName.

As you see that, I have also mentioned ON DUPLICATE KEY UPDATE with Insert Statement.
Now this statement first check the value of data and if data with the same key, then internally execute update statement otherwise executes the insert statement.
The Result:

MySQLMerge

This way you can use ON DUPLICATE KEY UPDATE statement to merge Insert and Update statement.

Some Important point:
If you are using ON DUPLICATE KEY UPDATE statement, then your table has must Primary key or at least one Unique Key.

If you are using Auto_Increment number, then your LAST_INSERT_ID() function will not work properly because if data updates, then also Auto_Increment ID will increase one number internally. This is one disadvantage of this statement.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

4 Comments on "Insert-Update, Merge statement in MySQL"

Notify of
avatar
Sort by:   newest | oldest | most voted
maunamopest
Guest

If I use merge statement only for Insert then does it is not good as use Insert statement. Please give your expert comments which one give better performance.

Nubia
Guest

I want forgathering useful information , this post has got me even more info! .

trackback

thank you anvesh for sharing this. you are doing good job…..

wpDiscuz