MySql Upsert Query

The previous lesson of MySql Lessons series was about MySql Triggers and History Table and in this lesson we will learn about MySql Upsert Query.



MySql Upsert Statement is used to insert a new row in a table if a duplicate key does not exist and update an existing row if a duplicate key exists. To elaborate the functionality of MySql Upsert Statement consider the `user` table given in the image below:



The above `user` table contains (id, name, username, email, password) of a user. We want to insert a new row in the `user` table if the `username` and `email` does not exist in the table already and update the password of the user if the `username` or `email` already exists in the table. The first thing we need to do to achieve this is make the `username` and `email` fields UNIQUE. MySql queries to make the `username` and `email` fields UNIQUE are given below:

ALTER TABLE `user` ADD UNIQUE(`username`);

ALTER TABLE `user` ADD UNIQUE(`email`);

Now that we have UNIQUE `username` and `email` fields in our `user` table, we can write MySql Upsert query to achieve our goal.

INSERT INTO `user` (`id`,`name`,`username`,`email`,`password`) 
VALUES (4,'John Malcom','john123','john123@gmail.com','12345abc')
  ON DUPLICATE KEY UPDATE `password` = 'newpass123';

The above query will update the password of user (John Malcom) as the `user` table already contains `username` john123 and `email` john123@gmail.com



Note that if the `username` and `email` fields are not UNIQUE for the `user` table, the above query will insert a new record and will not update the already existing data against user (John Malcom). Similarly the upsert query given below will insert a new record as the `username` john1234 and `email` john1234@gmail.com are UNIQUE in the table `user`.

INSERT INTO `user` (`id`,`name`,`username`,`email`,`password`) 
VALUES (4,'John Malcom','john1234','john1234@gmail.com','12345abc')
  ON DUPLICATE KEY UPDATE `password` = 'newpass123'

Stay tuned for more lessons...