MySQL
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.
Some useful MySQL functions that might make ones life easy
Many developers working in web development does not know the power of database system. They are continuously using the old techniques that makes mess in the code. In any application whether its a web or desktop application there needs many operations on data, some time we have to format data in various shapes to display them at many places. Most of the new developers use simple query to fetch any apply some built-in or custom function to format the data that makes too much code and could create lot of code management issue in future. I am going to share some useful function that could help you writing your queries and you really don’t need your custom or built-in function at many places, thus helps you writing clean and efficient code quickly.
-
DEFAULT(col_name): This function returns the default value of a table column.
Example:
UPDATE table_name SET col = DEFAULT(col)+1 WHERE id < 100;
-
FORMAT(X,D): It Formats the number X to a format like ‘#,###,###.##’ and round it D decimal palces and returns as a string.
Example:
SELECT FORMAT(12432.123456, 3);
Retruns: 12,432.123
-
INET_ATON(expr): It converts the Network (aka IP) address to a numeric presentation in integer format .
Example:
SELECT INET_ATON('192.168.137.1');Returns: 3232270593
-
INET_NTOA(expr): It converts the numeric Network address in network byte order (4 or 8 byte) to dotted quad representation as a string.
Example:
SELECT INET_NTOA(3232270593);
Returns: ’192.168.137.1′
- SLEEP(duration): This function causes a sleep or pause for give number of seconds given by duration parameter.
-
UUID(): This function give a Universal Unique Identifier. This identifier is generated according to “DCE 1.1: Remote Procedure Call”. UUID is designed to be unique in space and time. So two number generated by UUID() will return two different number even if calls were made on two different computers. UUID is a 128 bit number in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format. The first three numbers are generated form timestamp, the fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (consider the case: daylight saving time) and the fifth number is an IEEE 802 node number (Physical address or MAC) that provides spatial uniqueness. But this fifth number si only workin on FreeBSD and linux on other operating system MySQL uses a 48bit random number thus causes a very low probability of collision.
Example:
SELECT UUID();
Returns: ’6ccd780c-baba-1026-9564-0040f4311e29′
In my next posts I will post some useful string functions that will help you in routine programming. Come soon to enjoy my posts.