AnalyticDB:INSERT ON DUPLICATE KEY UPDATE

When the INSERT ON DUPLICATE KEY UPDATE statement is being executed, AnalyticDB for MySQL first attempts to insert a row of data into a table. If the data to be inserted uses the same primary key as existing data, the value specified in the clause of the INSERT ON DUPLICATE KEY UPDATE statement is used to update an existing row. AnalyticDB for MySQL conforms to the following rules to select statements based on whether the row to be written exists:

• If the row to be written does not exist, the INSERT statement is executed to insert the row. The number of affected rows is 1.
• If the row to be written exists, the UPDATE statement is executed to update the existing row. The number of affected rows is 1.

The student_course table is used in all examples. Execute the following statement to create the student_course table:

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:

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. 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 user_id column is set to 11056941 in both the first and second inserted rows. These two rows use the same primary key. After the preceding statement is executed, only the value of the course_name column in the ON DUPLICATE KEY UPDATE clause is updated to 'Industrial Accounting Practice V9.0--66',business_id = 'kuaiji'. If you execute the SELECT * FROM student_course; statement to view the updated data, the following results are 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      |

For example, you can 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 = 'Industrial Accounting Practice V9.0--77',
business_id = 'kuaiji';