On duplicate key update is a mariadb/mysql extension to the insert statement that, if it finds a duplicate unique or primary key, will instead perform an update. the row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the api's client_found_rows flag is set.. Insert into t1 (a,b,c) values (1,2,3) on duplicate key update c=c+1; update t1 set c=c+1 where a=1; (the effects are not identical for an innodb table where a is an auto-increment column. with an auto-increment column, an insert statement increases the auto-increment value but update does not.). You should be able to do insert into donkey (name, city) select name, city from mule m on duplicate key update donkey.city = values(city); values() pseudo-function returns the value that would be inserted into the column if insert succeeded..
Description: we noticed this strange behaviour from insert on duplicate key update when the auto_increment id reaches a maximum value or its boundary basing on integer types. so far the tests are based on unsigned integer types.. On duplicate key update" on mysql versions 5.5.8 and 5.5.14 (latest) the auto_increment field in the table is incremented by 1 when an update is performed (and insert). this means that doing multiple "insert into ... Insert into t1 (a,b,c) values (1,2,3) on duplicate key update c=c+1; update t1 set c=c+1 where a=1; (the effects are not identical for an innodb table where a is an auto-increment column. with an auto-increment column, an insert statement increases the auto-increment value but update does not.).
