MariaDB insert on duplicate key update

May 7, 05:15 AM

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 ;

Mark Edwards

,

---

Commenting is closed for this article.

---