Update multiple records by using ON DUPLICATE KEY UPDATE in MySQL

We 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.

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES (2, 'Max Ruin', 'Three', 86, 57, 86)

Duplicate entry '2' for key 'id'

Now same INSERT query query will update the record with new data instead of adding any new record.

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES (2, 'Max Ruin', 'Three', 86, 57, 86) on duplicate key update social=86,science=57,math=86

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.

If a new record is added ( inserted ) then number of affected rows = 1
If a record is updated with new data then number of affected rows = 2
If a new record is updated with same data then number of affected rows = 0

Updating Multiple records

By using same query we can update multiple records with new data.

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES
(2, 'Max Ruin', 'Three', 86, 57, 86),
(3, 'Arnold', 'Three', 56, 41, 76),
(4, 'Krish Star', 'Four', 62, 52, 72),
(5, 'John Mike', 'Four', 62, 82, 92),
(6, 'Alex John', 'Four', 58, 93, 83),
(7, 'My John Rob', 'Fifth', 79, 64, 74),
(8, 'Asruid', 'Five', 89, 84, 94),
(9, 'Tes Qry', 'Six', 77, 61, 71),
(10, 'Big John', 'Four', 56, 44, 56) 

ON DUPLICATE KEY UPDATE social=values(social),science=values(science),math=values(math);

Above query will update 9 records with new data. The message we will get is 18 rows inserted.

UPDATE with INSERT

Now 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 )

INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES
(2, 'Max Ruin', 'Three', 86, 57, 86),
(3, 'Arnold', 'Three', 56, 41, 76),
(4, 'Krish Star', 'Four', 62, 52, 72),
(5, 'John Mike', 'Four', 62, 82, 92),
(6, 'Alex John', 'Four', 58, 93, 83),
(7, 'My John Rob', 'Fifth', 79, 64, 74),
(8, 'Asruid', 'Five', 89, 84, 94),
(9, 'Tes Qry', 'Six', 77, 61, 71),
(10, 'Big John', 'Four', 56, 44, 56),
(11,'New Name','Five',75,78,52) 

ON DUPLICATE KEY UPDATE social=values(social),science=values(science),math=values(math);

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

ON DUPLICATE KEY UPDATE to update multiple records

Updating Multiple records

UPDATE with INSERT