Faisal Kaleem

A trick to insert update multiple records quickly and efficiently

In everyday programming we insert records and update them. Many developers use the INSERT query to insert data then use the UPDATE query to update data but before they actually need to get the WHERE condition to update the appropriate data. This task some times takes a lot of efforts if someone needs to insert multiple records simultaneously and it is more difficult to update those records simultaneously based on different condition for each record using the these traditional UPDATE and INSERT queries. Consider the bulk insert and update manipulation much similar to a data showed in a grid. Consider a simple example:

An admin has an interface to create new and update existing users/subscribers simultaneously. This interface has input boxes placed like a grid. He enters new record by inserting new row and make amendments to the existing users record. Think! how the system will recognize which records are new so that it could be inserted or updated in case existing users. The beginner level programmer probably say that we first will sort out all the submitted records in two categories new and old ones by making loop operation and sort the IDs in array and will perform the appropriate operation. Although this approach will work but results in a lot of messy code. Are you agree with me?

Now I am going to show my approach:

Consider the following query:

INSERT INTO users (id,email,first_name,last_name, dob) VALUES (1,'usman@abc.com','Usman','Khan','1989/10/30'), (2,'jamal@abc.com','Jamal','Nasir','1985/11/06') ON DUPLICATE KEY UPDATE first_name=VALUES(first_name), last_name=VALUES(last_name), dob=VALUES(dob);

The above query inserting the two records first time. As the id is primary key and email will surely unique so if we again run this query it will update all two records because of ON DUPLICATE KEY UPDATE triggers and updates the fields first_name, last_name and dob.

So you don’t need to check for the existing records using SELECT anymore.

Thank you guys for reading my post please feel free to put comments or critics. It will be highly appreciated.

Tags: inser update trick, on duplicate key update, quickly insert update