June 25 2013

MySQL sql duplicate insert update

Tagged Under : ,

MySQL
Duplicate row found and then update the row by one sql statement. This can be done by SQL, and not need any others server-side scripting.

Now let us see how it work. First, create table and insert some example data.
CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);

INSERT INTO `foo` (`id`, `name`) VALUES
(1, 'foo'),
(2, 'foo2');

From here we try insert a duplicate row.
INSERT INTO `foo` (`name`) VALUES ('foo2');
Server will return #1062 – Duplicate entry ‘test’ for key ‘name’, because `foo2` already there and ‘name‘ is UNIQUE KEY.

In Mysql have one function call ON DUPLICATE KEY UPDATE for UPDATE the row when DUPLICATED KEY found in the table.
INSERT INTO `foo` (`name`) VALUES ('foo2') 
ON DUPLICATE KEY UPDATE
`name` = 'foo3'
Above example are showing the example code and try run it.

Browser the table you will found that id 2 ‘name‘ already update to foo3.
+----+------+
| id | name |
+----+------+
|  1 | foo  |
|  2 | foo3 |
+----+------+

Make a Comment

*

code