This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on the merge statement for Insert-Update in MySQL.
For all the database developers merge statement is very popular to handle insert, update and delete into a single statement.
Microsoft SQL Server provides the merge statement in which you can manage Insert, update and delete and can make a single statement.
But unfortunately, MySQL server has no any default Merge Statement.
It has a similar alternative option for merging of Insert, Update.
You can use INSERT ON DUPLICATE KEY UPDATE in MySQL.
Let’s first create sample table with data:
|
1 2 3 4 5 6 7 8 9 |
CREATE DATABASE Employee; CREATE TABLE Employee.tbl_TestMerge ( EmpNumber INTEGER PRIMARY KEY, EmpName VARCHAR(50) ); INSERT INTO Employee.tbl_TestMerge VALUES (1,'ABC'),(2,'XYZ'),(3,'PQR') ,(4,'EFG'),(5,'JKL'); |
Use INSERT ON DUPLICATE KEY UPDATE:
|
1 2 3 4 5 |
INSERT INTO Employee.tbl_TestMerge (EmpNumber,EmpName) VALUES (2,'LKM') ON DUPLICATE KEY UPDATE EmpNumber=VALUES(EmpNumber),EmpName=VALUES(EmpName); |
In the above code, you can see EmpNumber is Primary Key and I am trying to insert same EmpNumber=2 with different EmpName.
As you can check that, I also mentioned ON DUPLICATE KEY UPDATE with Insert Statement.
Now, this statement first checks the value of data and if data with the same key, internally execute update statement otherwise it executes the insert statement.
The Result:
This way you can use ON DUPLICATE KEY UPDATE statement for merging Insert and Update statement.
Important points:
If you are using ON DUPLICATE KEY UPDATE statement, your table has must Primary key or at least one Unique Key.If you are using Auto_Increment number, your LAST_INSERT_ID() function will not work properly because if data updates, also Auto_Increment ID will increase a number internally. This is a disadvantage of this statement.
