When we execute the following SQL statement, we failed to update the row where the duplicate data is located.
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (t BLOB, n INT, UNIQUE (t));
INSERT INTO t1 VALUES ('Hrecvx_0004ln-00',1), ('Hrecvx_0004mm-00',1);
INSERT INTO t1 VALUES ('Hrecvx_0004mm-00',2) ON DUPLICATE KEY UPDATE n = VALUES 
;
Result: The SQL statement update the value of n in the line where Hrecvx_0004ln-00 is located. However, it should update the line where Hrecvx_0004mm-00 is located.
mysql> DROP TABLE IF EXISTS t1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> CREATE TABLE t1 (t BLOB, n INT, UNIQUE (t));
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
mysql> INSERT INTO t1 VALUES ('Hrecvx_0004ln-00',1), ('Hrecvx_0004mm-00',1);
|
Query OK, 2 rows affected (0.00 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
mysql> SELECT * FROM t1;
|
+------------------+------+
|
| t | n |
|
+------------------+------+
|
| Hrecvx_0004ln-00 | 1 |
|
| Hrecvx_0004mm-00 | 1 |
|
+------------------+------+
|
2 rows in set (0.00 sec)
|
|
|
mysql> INSERT INTO t1 VALUES ('Hrecvx_0004mm-00',2) ON DUPLICATE KEY UPDATE n = VALUES (n);
|
Query OK, 2 rows affected (0.00 sec)
|
|
|
mysql> SELECT * FROM t1;
|
+------------------+------+
|
| t | n |
|
+------------------+------+
|
| Hrecvx_0004ln-00 | 2 |
|
| Hrecvx_0004mm-00 | 1 |
|
+------------------+------+
|
2 rows in set (0.00 sec)
|
|