MySql - ON DUPLICATE KEY INSERT
With ON DUPLICATE KEY UPDATE
, you cannot insert into another table - nor is there an alternative function available.
Two custom/alternative ways you can accomplish this:
Using a
stored procedure
as outlined in the accepted answer to this question: MySQL ON DUPLICATE KEY insert into an audit or log tableCreating a
trigger
that logged everyINSERT
for your table into another table and querying the table full of "logs" for any duplicates.
Something similar to this should work:
CREATE TABLE insert_logs (
id int not null
);
delimiter |
CREATE TRIGGER insert_logs_trigger BEFORE INSERT ON your_table
FOR EACH ROW BEGIN
INSERT INTO insert_logs SET id = NEW.id;
END;
|
To get a list of the duplicates in the table, you could us:
SELECT id FROM insert_logs HAVING COUNT(id) > 1 GROUP BY id;