MySQL 5.5 INSERT... ON DUPLICATE KEY UPDATE statement description
Execute INSERT... ON DUPLICATE KEY UPDATE statement, if the value inserted by the INSERT statement duplicates an existing UNIQUE index or primary key, MySQL updates the existing row. Test tables without primary keys and UNIQUE indexes
Mysql > select * from dept2
+-+
| | deptno | dname | report_date | |
+-+
| | 10 | Research | 2016-06-03 |
| | 20 | Maintenance | 2016-06-03 |
| | 30 | Leader | 2016-06-03 |
| | 40 | Market | 2015-08-02 | |
+-+
4 rows in set (0.00 sec)
Mysql > desc dept2
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | deptno | int (5) | NO | MUL | NULL |
| | dname | varchar (14) | YES | | NULL |
| | report_date | date | YES | MUL | NULL |
+-+ +
3 rows in set (0.00 sec)
Mysql > INSERT INTO dept2 (deptno,dname,report_date) VALUES
-> ON DUPLICATE KEY UPDATE report_date='2010-10-30'
Query OK, 1 row affected (0.01sec)
Mysql > select * from dept2
+-+
| | deptno | dname | report_date | |
+-+
| | 10 | Research | 2016-06-03 |
| | 20 | Maintenance | 2016-06-03 |
| | 30 | Leader | 2016-06-03 |
| | 40 | Market | 2015-08-02 | |
| | 20 | Development | 2010-10-30 |
+-+
5 rows in set (0.00 sec)
Mysql > delete from dept2 where deptno=20 and report_date=date'2010-10-30'
Query OK, 1 row affected (0.01sec)
Mysql > select * from dept2
+-+
| | deptno | dname | report_date | |
+-+
| | 10 | Research | 2016-06-03 |
| | 20 | Maintenance | 2016-06-03 |
| | 30 | Leader | 2016-06-03 |
| | 40 | Market | 2015-08-02 | |
+-+
4 rows in set (0.00 sec)
Add the primary key and then test
Mysql > alter table dept2 add primary key (deptno)
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > INSERT INTO dept2 (deptno,dname,report_date) VALUES
-> ON DUPLICATE KEY UPDATE report_date='2010-10-30'
Query OK, 2 rows affected (0.14 sec)
Mysql > select * from dept2
+-+
| | deptno | dname | report_date | |
+-+
| | 10 | Research | 2016-06-03 |
| | 20 | Maintenance | 2010-10-30 |
| | 30 | Leader | 2016-06-03 |
| | 40 | Market | 2015-08-02 | |
+-+
4 rows in set (0.00 sec)
