Shantanu's Blog

A detailed analysis of what happens when we use different options to insert/ update a record.

Here is a sample table...
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+

We want to insert a new record with emp_id 3, name - anthony and the address should be 'South Africa'. If the record is already present in the DB it should be updated.

This may look like a simple task, but checking the table structure, it is clear that there is no composite unique index, but separate per column unique keys.

CREATE TABLE `mykey` (
`id` int(11) NOT NULL auto_increment,
`emp_id` int(11) default NULL,

`name` varchar(100) default NULL,
`address` varchar(100) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `emp_id` (`emp_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB

The challenge is to write an insert/ update statement that will satisfy all constraint and will fail predictably only in certain cases.

1) "insert ignore" will prefer not to do anything.
2) "replace into" may look like the best choice, but it will also change the auto_incremented ID and has some issues when there is more than one unique key.
For e.g. in the following example, while trying to insert emp_id 3 with name - akbar, it actually removed one row.

3) I guess the "on duplicate key update " clause is the right choice. It does not change the auto_id while updating the record. And even if there are no composite unique indexes in the table, it works as if they are present.

mysql> drop table if exists mykey;
Query OK, 0 rows affected (0.02 sec)

mysql> create table mykey(id int not null auto_increment, emp_id int, name varchar(100), address varchar(100), primary key (id), unique(emp_id), unique(name)) engine=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into mykey (emp_id, name, address) values (1, 'amar', 'hyderabad, AP, india') ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into mykey (emp_id, name, address) values (2, 'akbar', 'karachi, pakistan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mykey (emp_id, name, address) values (3, 'anthony', 'london, UK') ;
Query OK, 1 row affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint savepoint_one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)
_____

# Case 1: "insert ignore"

mysql> insert ignore into mykey (emp_id, name, address) values (3, 'anthony', 'South Africa') ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

_____

# Case 2: "replace into"

mysql> replace into mykey (emp_id, name, address) values (3, 'anthony', 'South Africa') ;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 4 | 3 | anthony | South Africa |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

mysql> rollback to savepoint_one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

mysql> replace into mykey (emp_id, name, address) values (3, 'akbar', 'South Africa') ;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+-------+----------------------+
| id | emp_id | name | address |
+----+--------+-------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 5 | 3 | akbar | South Africa |
+----+--------+-------+----------------------+
2 rows in set (0.00 sec)
_____

# Case 3: "on duplicate key"

mysql> rollback to savepoint_one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.01 sec)

mysql> insert into mykey (emp_id, name, address) values (3, 'akbar', 'South Africa') on duplicate key update emp_id = 3, name = 'akbar', address='South Africa' ;
ERROR 1062 (23000): Duplicate entry 'akbar' for key 3

mysql> insert into mykey (emp_id, name, address) values (3, 'akbar', 'South Africa') on duplicate key update emp_id = 3, name = 'anthony', address='South Africa' ;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | South Africa |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

Labels: mysql FAQ, mysql tips