Handle Duplicate Rows with INSERT ON DUPLICATE KEY UPDATE - AnalyticDB for MySQL

INSERT ON DUPLICATE KEY UPDATE (commonly called upsert) inserts a row into a table. If a row with the same primary key already exists, it updates that row instead.

AnalyticDB for MySQL applies the following logic:

Condition	Action	Affected rows
No conflict	Runs `INSERT`	1
Primary key conflict	Runs `UPDATE` on the existing row	1

Usage notes

The ON DUPLICATE KEY UPDATE clause supports literal value assignments and `VALUES()` assignments only. Arithmetic expressions, conditional expressions, and functions are not supported. If the clause references an unsupported expression type, an error occurs.

-- Supported: literal value assignment
ON DUPLICATE KEY UPDATE course_name = 'Intro to SQL'

-- Supported: VALUES() assignment
ON DUPLICATE KEY UPDATE course_name = VALUES(course_name)

-- Not supported: arithmetic expression (causes an error)
ON DUPLICATE KEY UPDATE count = count + 1

For large-volume writes or high-frequency writes (more than 100 QPS), CPU utilization may increase significantly. Use REPLACE INTO for batch updates instead.

Syntax

INSERT INTO table_name[(column_name[, …])]
[VALUES]
[(value_list[, …])]
ON DUPLICATE KEY UPDATE
   c1 = v1,
   c2 = v2,
   ...;

Examples

The examples below use the student_course table. Create it with the following statement:

CREATE TABLE student_course(
    id bigint,
    user_id bigint,
    nc_id varchar,
    nc_user_id varchar,
    nc_commodity_id varchar,
    course_no varchar,
    course_name varchar,
    business_id varchar,
    PRIMARY KEY(user_id)
) DISTRIBUTED BY HASH(user_id);

Insert a new row

The following statement inserts a row. Because no row with user_id = 11056941 exists yet, AnalyticDB for MySQL inserts it.

INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--55',
business_id = 'kuaiji';

Query the table to confirm the insert:

SELECT * FROM student_course;

Expected output:

+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+
| id    | user_id  | nc_id               | nc_user_id          | nc_commodity_id     | course_no | course_name                             | business_id|
+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+
|277941 | 11056941 | 1001EE1000000043G2T5|1001EE1000000043G2TO | 1001A5100000003YABO2|   kckm303 | Industrial Accounting Practice V9.0--55 |   kuaiji   |
+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+

Update an existing row

The following statement uses user_id = 11056941, which matches the existing row. AnalyticDB for MySQL updates only the columns specified in the ON DUPLICATE KEY UPDATE clause (course_name and business_id). All other columns remain unchanged.

INSERT INTO student_course(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277942, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--66', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--66',
business_id = 'kuaiji';

Query the table again:

SELECT * FROM student_course;

Expected output — id stays 277941 (unchanged), and course_name is updated to --66:

+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+
| id    | user_id  | nc_id               | nc_user_id          | nc_commodity_id     | course_no | course_name                             | business_id|
+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+
|277941 | 11056941 | 1001EE1000000043G2T5|1001EE1000000043G2TO | 1001A5100000003YABO2|   kckm303 | Industrial Accounting Practice V9.0--66 |   kuaiji   |
+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+

FAQ

Why do I get "insert on duplicate key update statement only support 'primitive value' and values() expr"?

The ON DUPLICATE KEY UPDATE clause only accepts literal values or VALUES() assignments. Arithmetic expressions (such as count + 1), conditional expressions, and functions are not supported.

Fix the statement by switching to a literal assignment or a VALUES() assignment:

Literal value assignment:

INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--55',
business_id = 'kuaiji';

`VALUES()` assignment:

INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = VALUES(course_name),
business_id = VALUES(business_id);

Why do I get "Error: Field 'xxxx' doesn't have a default value"?

The column is defined as NOT NULL without a default value, and the INSERT statement does not supply a value for it.

Fix it with either approach:

Add the missing column value to the INSERT INTO ... ON DUPLICATE KEY UPDATE statement.
Alter the table to add a default value for the column, then re-run the statement.

Can I insert multiple rows in a single statement?

Yes. Provide multiple value sets in the VALUES clause and handle conflicts in the ON DUPLICATE KEY UPDATE clause.

The following statement inserts three rows. For any row whose user_id matches an existing row, only course_name and business_id are updated.

INSERT INTO student_course(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277943, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--77', 'kuaiji'),
(277944, 11056943, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--88', 'kuaiji'),
(277945, 11056944, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--99', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = VALUES(course_name),
business_id = VALUES(business_id);