How to know if when using "on duplicate key update" a row was inserted or updated?

mysql

Solution

Add an `update_count INT NOT NULL DEFAULT 1` column and change your query:

INSERT
INTO    table (id, col1, col2, col3)
VALUES
(id_value, val1, val2, val3),
(id_value, val1, val2, val3,),
(id_value, val1, val2, val3),
(id_value, val1, val2, val3),
ON DUPLICATE KEY
UPDATE 
        col1 = VALUES (col1), 
        col2 = VALUES (col2), 
        col3 = VALUES (col3),
        update_count = update_count + 1;

You can also increment it in a `BEFORE UPDATE` trigger which will allow you to keep the query as is.

Problem

We have a database that gets updated everyday at midnight with a cronjob, we get new data from an external XML. What we do is that we insert all the new content and in case there is a duplicated key we update that field. ``` INSERT INTO table (id, col1, col2, col3) values (id_value, val1, val2, val3), (id_value, val1, val2, val3), (id_value, val1, val2, val3), (id_value, val1, val2, val3), ON DUPLICATE KEY UPDATE col1 = VALUES (col1), col2 = VALUES (col2), col3 = VALUES (col3); ``` What we want to know is which rows have actually been inserted, meaning we want to have a list of the new items. is there any query that might return the new inserts? Basically we will need to get all the new ID's and not the number of new insertions. Thanks

Original source