ON DUPLICATE KEY UPDATE to update multiple recordsWe know by using Insert command we can add records, but by using same insert command we can update multiple records of a table.
In our student table we have one unique auto increment field as ID. Here we can't have two records with same id. So if we try to use any insert query to add a record ( say with id=2 ) with already existing id ( Duplicate Key ) then we will get error message saying
Duplicate entry '2' for key 'id'Now we can specify in our query that in such exception cases ( of having duplicate id ) instead of inserting new record the existing record can be updated with new data.
New record will be inserted if no duplicate key is found or if we are not violating unique constraints set by the table property.
Here is an example which will generate an error message as we are violating unique constraints.
Duplicate entry '2' for key 'id'Now same INSERT query query will update the record with new data instead of adding any new record.
We will get a message saying 2 rows inserted, but actually we have updated one record only. Here mysql will retrun the number of affected rows based on the action it performed.
Updating Multiple recordsBy using same query we can update multiple records with new data.
Above query will update 9 records with new data. The message we will get is 18 rows inserted.
UPDATE with INSERTNow let us try updating existing 9 records and add one new record with a new id ( Don't forget to reset table data to old values )
Now 9 records will be updated with new data and one new record ( with id =11 ) will be added . The message we will get is 19 rows inserted.
Download sql dump of student3 table
This article is written by plus2net.com team.
More on Alter or coy table , add update or delete records in SQL