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.