MariaDB insert on duplicate key update

it appears that ‘insert on duplicate key update’ will actually create some sort of temporary record then delete it. you can see this in the AUTO_INCREMENT value.

https://mariadb.com/kb/en/insert-on-duplicate-key-update/#:~:text=INSERT%20…,API’s%20CLIENT_FOUND_ROWS%20flag%20is%20set.

DROP TABLE IF EXISTS testTable ;

CREATE TABLE testTable
( nameFirst VARCHAR NOT NULL
, nameLast VARCHAR NOT NULL
, addr VARCHAR NULL
, city VARCHAR NULL
, id INTEGER NOT NULL AUTO_INCREMENT
, UNIQUE KEY (nameFirst, nameLast)
, PRIMARY KEY (id)
);

INSERT INTO testTable SET nameFirst = ‘mark’ , nameLast = ‘edwards’ , addr = ’123 swallow lane’ ;

SELECT * FROM testTable;

INSERT INTO testTable SET nameFirst = ‘pete’ , nameLast = ‘robinson’ , addr = ’246 starling’ , city = ‘oceanside’ ON DUPLICATE KEY UPDATE addr = ’123 swallow lane’ , city = ‘oceanside, ca’ ;

SELECT * FROM testTable;

INSERT INTO testTable SET nameFirst = ‘pete’ , nameLast = ‘robinson’ , addr = ’246 starling’ , city = ‘oceanside’ ON DUPLICATE KEY UPDATE city=‘oceanside, ca’ ;

SELECT * FROM testTable;

INSERT INTO testTable SET nameFirst = ‘lori’, nameLast=‘edwards’, addr = ’948 south third’ ON DUPLICATE KEY UPDATE addr = ’123 swallow ln’, city=‘oceanside’ ;

SELECT * FROM mark ;

INSERT INTO testTable SET nameFirst = ‘lori’, nameLast=‘edwards’, addr = ’948 south third’ ON DUPLICATE KEY UPDATE addr = ’123 swallow ln’, city=‘oceanside’ ;

SELECT * FROM testTable ;

INSERT INTO testTable SET nameFirst = ‘marky’, nameLast=‘edwards’, addr = ’123 swallow lane’ ON DUPLICATE KEY UPDATE addr = ’123 swallow lane’, city=‘oceanside, ca’ ;

SELECT * FROM testTable ;