INSERT … ON DUPLICATE KEY UPDATE¶

INSERT … ON DUPLICATE KEY UPDATE When inserting data into a database table, update the data if it already exists, otherwise insert new data.

Grammar description¶

INSERT ... ON DUPLICATE KEY UPDATE When inserting data into a database table, update the data if it already exists, otherwise insert new data.

The INSERT INTO statement is the standard statement used to insert data into a database table; the ON DUPLICATE KEY UPDATE statement is used to update when there are duplicate records in the table. If a record with the same primary key exists in the table, use the UPDATE clause to update the corresponding column value, otherwise use the INSERT clause to insert a new record.

INSERT ... ON DUPLICATE KEY UPDATE detects conflicts on both PRIMARY KEY and secondary UNIQUE KEY constraints. When a conflict is found, the row is updated instead of inserted. The conflict resolution priority is: PRIMARY KEY > UNIQUE KEY (in definition order). If an incoming row conflicts on multiple unique keys against different existing rows, the first conflicting key takes precedence.

ON DUPLICATE KEY UPDATE supports all table types including:

Tables without an explicit primary key (fake PK + UNIQUE KEY)
Tables with foreign keys
Tables with fulltext indexes
Tables with ivfflat (vector) indexes
Tables with prefix unique indexes
Tables with ON UPDATE CURRENT_TIMESTAMP columns

Grammar structure¶

> INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...];

Examples¶

CREATE TABLE user (
    id INT(11) NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT(3) NOT NULL
);
-- Insert a new piece of data, the id doesn't exist, so enter the new data.
INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
ON DUPLICATE KEY UPDATE name='Tom', age=18;

mysql> select * from user;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | Tom  |   18 |
+------+------+------+
1 row in set (0.01 sec)

-- Increases the age field of an existing record by 1, while leaving the name field unchanged.
INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
ON DUPLICATE KEY UPDATE age=age+1;

mysql> select * from user;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | Tom  |   19 |
+------+------+------+
1 row in set (0.00 sec)

-- Inserts a new row, updating the name and age fields to the specified values.
INSERT INTO user (id, name, age) VALUES (2, 'Lucy', 20)
ON DUPLICATE KEY UPDATE name='Lucy', age=20;

mysql> select * from user;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | Tom  |   19 |
|    2 | Lucy |   20 |
+------+------+------+
2 rows in set (0.01 sec)

Advanced Examples¶

Tables Without Primary Key (Fake PK + UNIQUE KEY)¶

Tables that have a UNIQUE KEY but no explicit PRIMARY KEY are fully supported. The system uses a hidden fake primary key for internal row identification.

DROP TABLE IF EXISTS t_odku_fakepk;
CREATE TABLE t_odku_fakepk (a INT, b VARCHAR(20), UNIQUE KEY(a));

INSERT INTO t_odku_fakepk VALUES (1, 'x');
-- Conflict on UNIQUE KEY -> update
INSERT INTO t_odku_fakepk VALUES (1, 'y') ON DUPLICATE KEY UPDATE b = 'updated';
SELECT * FROM t_odku_fakepk ORDER BY a;

-- No conflict -> insert new row
INSERT INTO t_odku_fakepk VALUES (2, 'new') ON DUPLICATE KEY UPDATE b = 'z';
SELECT * FROM t_odku_fakepk ORDER BY a;

DROP TABLE IF EXISTS t_odku_fakepk;

Foreign Key Tables¶

ON DUPLICATE KEY UPDATE works on child tables with foreign key constraints. Foreign key validation is row-scoped: it validates only the statement’s own rows, not the entire table. ON DUPLICATE KEY UPDATE referencing a valid parent updates successfully; referencing a non-existent parent fails.

DROP TABLE IF EXISTS t_odku_child;
DROP TABLE IF EXISTS t_odku_parent;
CREATE TABLE t_odku_parent (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE t_odku_child (
    cid INT PRIMARY KEY,
    pid INT,
    v INT,
    FOREIGN KEY (pid) REFERENCES t_odku_parent(id)
);

INSERT INTO t_odku_parent VALUES (1, 'p1'), (2, 'p2');
INSERT INTO t_odku_child VALUES (10, 1, 100);

-- ODKU conflict on PK, update non-FK column
INSERT INTO t_odku_child VALUES (10, 1, 999) ON DUPLICATE KEY UPDATE v = 999;
SELECT * FROM t_odku_child ORDER BY cid;

-- ODKU insert with valid FK
INSERT INTO t_odku_child VALUES (20, 2, 200) ON DUPLICATE KEY UPDATE v = 200;
SELECT * FROM t_odku_child ORDER BY cid;

DROP TABLE IF EXISTS t_odku_child;
DROP TABLE IF EXISTS t_odku_parent;

Fulltext and Vector Index Tables¶

Tables with fulltext indexes or ivfflat (vector) indexes use the modern insert path. Irregular indexes are stripped from the insert pipeline and maintained asynchronously, so ON DUPLICATE KEY UPDATE works correctly. When the fulltext-indexed column itself is updated via ODKU, old tokens are dropped and new tokens are indexed synchronously.

DROP TABLE IF EXISTS t_odku_ft;
CREATE TABLE t_odku_ft(id INT PRIMARY KEY, uk INT UNIQUE, body TEXT, val INT);
INSERT INTO t_odku_ft VALUES (1, 10, 'hello world', 100), (2, 20, 'foo bar', 200);
-- Expected-Success: false (requires experimental_fulltext_index=1)
CREATE FULLTEXT INDEX ftidx ON t_odku_ft(body);

-- PK conflict on fulltext-indexed table
INSERT INTO t_odku_ft VALUES (1, 99, 'changed', 5) ON DUPLICATE KEY UPDATE val = val + 1;
SELECT id, uk, val FROM t_odku_ft ORDER BY id;
DROP TABLE IF EXISTS t_odku_ft;

NULL Unique Key Handling¶

An all-NULL unique key never conflicts with another all-NULL row. Each INSERT ... ON DUPLICATE KEY UPDATE with all-NULL unique key values inserts a new row rather than updating an existing one. Non-NULL values on the same unique key still follow normal ODKU semantics.

DROP TABLE IF EXISTS t_odku_null_unique;
CREATE TABLE t_odku_null_unique (a INT UNIQUE KEY, b INT);
-- Both insert new rows because NULL never conflicts with NULL
INSERT INTO t_odku_null_unique VALUES (NULL, NULL) ON DUPLICATE KEY UPDATE b = VALUES(b);
INSERT INTO t_odku_null_unique VALUES (NULL, NULL) ON DUPLICATE KEY UPDATE b = VALUES(b);
SELECT COUNT(*) AS row_count FROM t_odku_null_unique;

-- Non-NULL keys follow normal ODKU semantics
INSERT INTO t_odku_null_unique VALUES (1, 10) ON DUPLICATE KEY UPDATE b = VALUES(b);
INSERT INTO t_odku_null_unique VALUES (1, 20) ON DUPLICATE KEY UPDATE b = VALUES(b);
SELECT a, b FROM t_odku_null_unique WHERE a = 1;
DROP TABLE IF EXISTS t_odku_null_unique;

ON UPDATE CURRENT_TIMESTAMP No-Op Detection¶

When a table has a column with ON UPDATE CURRENT_TIMESTAMP, a no-op ODKU (where the written value equals the existing value) does not advance the timestamp. Only a genuine value change triggers the timestamp update.

DROP TABLE IF EXISTS t_odku_onupdate;
CREATE TABLE t_odku_onupdate (
  id INT PRIMARY KEY,
  v INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO t_odku_onupdate(id, v) VALUES (1, 10);

-- No-op update: timestamp does NOT advance
INSERT INTO t_odku_onupdate(id, v) VALUES (1, 10) ON DUPLICATE KEY UPDATE v = v;
SELECT v, updated_at FROM t_odku_onupdate WHERE id = 1;

-- Real change: timestamp advances
INSERT INTO t_odku_onupdate(id, v) VALUES (1, 99) ON DUPLICATE KEY UPDATE v = VALUES(v);
SELECT v, updated_at FROM t_odku_onupdate WHERE id = 1;
DROP TABLE IF EXISTS t_odku_onupdate;

Multi-Column Unique Key Conflict Resolution¶

When multiple UNIQUE KEY constraints exist, conflict priority is: PRIMARY KEY > UNIQUE KEY (in definition order). If an incoming row conflicts on multiple unique keys against different existing rows, the first conflicting key in definition order wins and its row is updated.

DROP TABLE IF EXISTS t_odku_realpk;
CREATE TABLE t_odku_realpk (
    id INT PRIMARY KEY,
    uk1 INT UNIQUE,
    uk2 INT UNIQUE,
    val INT
);
INSERT INTO t_odku_realpk VALUES (1, 10, 100, 1000), (2, 20, 200, 2000);

-- PK conflict (highest priority): updates row id=1
INSERT INTO t_odku_realpk VALUES (1, 99, 999, 5) ON DUPLICATE KEY UPDATE val = val + 1;
SELECT * FROM t_odku_realpk ORDER BY id;

-- Cross-row conflict: uk1 hits row 1, uk2 hits row 2 -> uk1 wins (definition order)
INSERT INTO t_odku_realpk VALUES (4, 10, 200, 5) ON DUPLICATE KEY UPDATE val = val + 1;
SELECT * FROM t_odku_realpk ORDER BY id;

-- In-batch duplicate protection: two new rows sharing a new unique-key value still error
INSERT INTO t_odku_realpk VALUES (20, 77, 701, 5), (21, 77, 702, 5) ON DUPLICATE KEY UPDATE val = val + 1;

DROP TABLE IF EXISTS t_odku_realpk;

Prefix Unique Index ODKU¶

ODKU conflict resolution on prefix unique indexes uses the stored prefix key. Rows sharing the same prefix at the index column are treated as conflicts, and the existing row is updated.

DROP TABLE IF EXISTS t_odku_prefix;
CREATE TABLE t_odku_prefix(id INT PRIMARY KEY, body VARCHAR(64), v INT, UNIQUE KEY u(body(4)));
INSERT INTO t_odku_prefix VALUES (1, 'abcdxxxx', 10);
-- 'abcdyyyy' shares 4-char prefix 'abcd' with existing row -> conflict -> update
INSERT INTO t_odku_prefix VALUES (2, 'abcdyyyy', 20) ON DUPLICATE KEY UPDATE v = v + 100;
SELECT id, body, v FROM t_odku_prefix ORDER BY id;
DROP TABLE IF EXISTS t_odku_prefix;

Restrictions¶

For tables without any PRIMARY KEY or UNIQUE KEY, ON DUPLICATE KEY UPDATE degrades to a plain INSERT (no duplicate concept without a key).
INSERT ... ON DUPLICATE KEY UPDATE on child tables validates foreign keys row-scoped: pre-existing orphan rows in the table are ignored, but the final row image produced by the statement must satisfy all FK constraints.