Efficient Techniques for Replacing Duplicate Records in MySQL

To replace duplicate records and avoid any error while inserting, use INSERT ON DUPLICATE KEY UPDATE. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Id int,
   -> Name varchar(20),
   -> UNIQUE(Id,Name)
   -> );
Query OK, 0 rows affected (0.78 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(101,'Chris') on duplicate key update Id=10001,Name='Robert';
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(102,'Mike') on duplicate key update Id=10001,Name='Robert';
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(101,'Chris') on duplicate key update Id=10001,Name='Robert';
Query OK, 2 rows affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------+--------+
|    Id | Name   |
+-------+--------+
|   102 | Mike   |
| 10001 | Robert |
+-------+--------+
2 rows in set (0.00 sec)