All Products
Search

This Product

    AnalyticDB:INSERT ON DUPLICATE KEY UPDATE

    Document Center

    AnalyticDB:INSERT ON DUPLICATE KEY UPDATE

    Last Updated:Mar 12, 2025

    When using INSERT ON DUPLICATE KEY UPDATE with AnalyticDB for MySQL, the service first tries to insert a new row. If there's a primary key conflict with existing data, it updates the row with the matching primary key instead.

    AnalyticDB for MySQL determines the appropriate execution statement based on the existence of the row to be written. The rules are:

    • If the row does not exist, INSERT is executed to add a new row. The number of affected rows is 1.

    • If the row exists, UPDATE is executed to modify the existing row. The number of affected rows is also 1.

    Precautions

    • INSERT INTO ... ON DUPLICATE KEY UPDATE... supports only equality updates and does not support complex expressions, including arithmetic and conditional expressions.

    • In scenarios with large data volumes or high update frequencies (over 100 QPS), using INSERT ON DUPLICATE KEY UPDATE can significantly increase CPU utilization. For batch updates, REPLACE INTO is recommended. For more information, see REPLACE INTO.

    Syntax

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

    Examples

    The examples in this topic use the student_course table. The table creation statement is as follows:

    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);

    To insert a row of data, use the following statement:

    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';

    After executing the SELECT * FROM student_course; statement, the result confirms that the data was inserted successfully:

    +-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| 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   |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+

    Next, to insert a new row into the student_course table, use the following statement:

    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';

    However, since the new data has a duplicate primary key (both user_id are 11056941), the statement will only update the ON DUPLICATE KEY UPDATE clause with the values course_name = 'Industrial Accounting Practice V9.0--66', business_id = 'kuaiji'. To view the updated data, execute the SELECT * FROM student_course; statement. The result is as follows:

    +-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| 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

    How to resolve the error `insert on duplicate key update` statement only support 'primitive value' and values() expr.

    This error occurs because INSERT INTO ... ON DUPLICATE KEY UPDATE... supports only basic value assignments or VALUES() assignments, not complex expressions or functions. If complex expressions or functions are used in ON DUPLICATE KEY UPDATE, this error will occur.

    To fix this, modify the SQL statement to use basic value assignments or VALUES() assignments in ON DUPLICATE KEY UPDATE.

    Example 1: Basic 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';

    Example 2: 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);

    INSERT ON DUPLICATE KEY UPDATE does it support batch data insertion?

    Yes. INSERT ON DUPLICATE KEY UPDATE allows for batch data insertion. You can provide multiple sets of values using the VALUES clause and specify conflict resolution in the ON DUPLICATE KEY UPDATE clause.

    For instance, to batch insert three records into the student_course table, use the following statement:

    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);