MySQL: Using “ON DUPLICATE KEY UPDATE” while inserting record

MySQL: Using “ON DUPLICATE KEY UPDATE” while inserting record

Sometimes we may have the situation where multiple users insert record into the same table simultaneously . Lets say the primary key for a table is an integer field and user executes an insert query by giving the value for the Id (Primary Key) field as “1”. But at the same time another user has already inserted a record with the same Id value as 1, In that case MySQL will throw an exception.   To overcome from this type of situation we can use “ON DUPLICATE KEY UPDATE”. This will check if any record with the same value for Id is already inserted. If so then it will update the field(s) which you will mention. Otherwise it will simply insert the record. The syntax will be like this :- INSERT INTO [TableName] (Col1,Col2,Col3,…) VALUES (Val1,Val2,Val3,…) ON DUPLICATE KEY UPDATE Col2= val2,Col3 = Val3,…;

Example: – I have a users table having following records :- Id       FirstName       LastName     JobTitle    AlreadyInserted 1        Albert          Roger     Software Engg     0     2        albert          Joseph    w Engg            0 3        Bob             Bette     Software Engg     0  INSERT INTO users (Id,FirstName,LastName,JobTitle,AlreadyInserted) values(1,‘Albert‘,’Roger’,’Software Engg’,0); When I will execute this query it will give me an error as “Duplicate entry ‘1’ for key 1” INSERT INTO users (Id,FirstName,LastName,JobTitle,AlreadyInserted) values(1,’Albert’,’Roger’,’Software Engg’,0) ON DUPLICATE KEY UPDATE AlreadyInserted = 1;  This query will simply update the existing record. And the resultset will be like this :- Id       FirstName       LastName     JobTitle    AlreadyInserted 1        Albert          Roger     Software Engg     1     2        albert          Joseph    n/w Engg          0 3        Bob             Bette     Software Engg     0  Instead of using two queries SELECT and INSERT/UPDATE we can use a single query. This can also be used for any UNIQUE Key field.