Chevrons Code

PHP, ASP.net, HTML, JavaScript, Yii and 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 |
+----+------+