In a situation in which a duplicate value is inserted into a column for which the UNIQUE index or the PRIMARY KEY constraint has been set, you can update to a new value without outputting the error by specifying the ON DUPLICATE KEY UPDATE clause in the INSERT statement.
However, the ON DUPLICATE KEY UPDATE clause cannot be used in a table in which a trigger for INSERT or UPDATE has been activated, or in a nested INSERT statement.
<INSERT … VALUES statement>
<INSERT … SET statement>
<INSERT … SELECT statement>
INSERT ...
[ON DUPLICATE KEY UPDATE column_name = expr, ... ]
--creating a new table having the same schema as a_tbl1
CREATE TABLE a_tbl3 LIKE a_tbl1;
INSERT INTO a_tbl3 SELECT * FROM a_tbl1 WHERE id IS NOT NULL and name IS NOT NULL;
SELECT * FROM a_tbl3;
id name phone
=========================================================
1 'aaa' '000-0000'
2 'bbb' '000-0000'
3 'ccc' '333-3333'
6 'eee' '000-0000'
--insert duplicated value violating UNIQUE constraint
INSERT INTO a_tbl3 VALUES(2, 'bbb', '222-2222');
ERROR: Operation would have caused one or more unique constraint violations.
--insert duplicated value with specifying ON DUPLICATED KEY UPDATE clause
INSERT INTO a_tbl3 VALUES(2, 'bbb', '222-2222')
ON DUPLICATE KEY UPDATE phone = '222-2222';
SELECT * FROM a_tbl3 WHERE id=2;
id name phone
=========================================================
2 'bbb' '222-2222'