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.

Anvesh Patel

Leave a Reply

5 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…..

trackback

perferct, working for me.

wpDiscuz