INSERT INTO `student3` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES (2, 'Max Ruin', 'Three', 86, 57, 86)
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 return the number of affected rows based on the action it performed.
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.
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.
13-10-2021 | |
really good explanation! Thanks! |
13-10-2021 | |
Really good article for begineers |