INSERT ON DUPLICATE KEY UPDATE to insert or update data - AnalyticDB

When you execute the INSERT ON DUPLICATE KEY UPDATE statement to update data in a table, AnalyticDB for MySQL first attempts to insert rows of data into the table. If a row to be inserted uses the same primary key as an existing row, AnalyticDB for MySQL updates the existing row.

AnalyticDB for MySQL selects SQL statements based on the following rules:

If the row to be inserted does not exist, AnalyticDB for MySQL uses the INSERT statement to insert the row. The number of affected rows is 1.
If the row to be inserted exists, AnalyticDB for MySQL uses the UPDATE statement to update the existing row. The number of affected rows is 1.

Usage notes

You can execute the INSERT INTO ... ON DUPLICATE KEY UPDATE... statement to update data using value assignments, but not complex expressions such as arithmetic or conditional expressions.
If you execute the INSERT ON DUPLICATE KEY UPDATE statement to update a large amount of data or update data at a high frequency (more than 100 QPS), the CPU utilization may significantly increase. We recommend that you execute the REPLACE INTO statement to update data in batches. 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

In these examples, the student_course table is used. Execute the following statement to create a table named student_course:

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

Execute the following statement to insert a row of data into the student_course table:

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

Execute the SELECT * FROM student_course; statement to query data in the student_course table. The following query result indicates that a row of data is inserted:

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

Execute the following statement to insert another row of data into the student_course table:

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

The row that you want to insert uses the same primary key (user_id column: 11056941) as the first inserted row. After the preceding statement is executed, only the values in the ON DUPLICATE KEY UPDATE clause (course_name = 'Industrial Accounting Practice V9.0--66',business_id = 'kuaiji') are updated. After you execute the SELECT * FROM student_course; statement to query the updated data, the following result is returned:

+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| 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 do I resolve the error `insert on duplicate key update` statement only support 'primitive value' and values() expr.

Cause: The INSERT INTO ... ON DUPLICATE KEY UPDATE... statement supports literal value assignments (Example 1) or VALUES() assignments (Example 2), but not complex expressions (such as arithmetic and conditional expressions) or functions. If the ON DUPLICATE KEY UPDATE clause references a complex expression or function, an error occurs.

Solution: Modify the SQL statement using literal value assignments or VALUES() assignments in the ON DUPLICATE KEY UPDATE clause.

Example 1: literal value assignments.

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() assignments.

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

How do I resolve the error Error : Field 'xxxx' doesn't have a default value?

Cause: When creating the table, the column is specified as NOT NULL (i.e., column_name column_datatype NOT NULL), but no default value is specified for this column. When executing INSERT INTO ... ON DUPLICATE KEY UPDATE... to insert data, no data is inserted into this column, and the column has no default value, resulting in the above error.

Solution: You can use either of the following methods.

Modify the INSERT INTO ... ON DUPLICATE KEY UPDATE... statement to insert specific data into the specified column.
Modify the table schema to add a default value for the specified column, and then re-execute the INSERT INTO ... ON DUPLICATE KEY UPDATE... statement.

Can I execute the `INSERT ON DUPLICATE KEY UPDATE` statement to insert data in batches?

Yes, you can execute this statement to insert data in batches. When you execute the INSERT ON DUPLICATE KEY UPDATE statement to insert data in batches, you can provide multiple sets of values in the VALUES clause and specify how to handle conflicts in the ON DUPLICATE KEY UPDATE clause.

For example, execute the following statement to insert three rows of data into the student_course table:

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