You can use the INSERT ON DUPLICATE KEY UPDATE statement to insert data. If the data to be inserted causes duplicate keys, only values specified in the ON DUPLICATE KEY UPDATE clause are updated.

Syntax

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

Examples

Typically, this statement first attempts to insert a new row into a table. If an error occurs due to duplicate keys, the statement updates an existing row by using the value specified in the ON DUPLICATE KEY UPDATE clause. MySQL returns the following numbers of affected rows based on the operations that are performed:

  • If a new row is inserted, the number of affected rows is 1.
  • If an existing row is updated, the number of affected rows is 2.
  • If an existing row is updated by using its current value, the number of affected rows is 0.

Insert a new 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';

Insert another row of data. This row causes duplicate keys. Therefore, only course_name = 'Industrial Accounting Practice V9.0--66',business_id = 'kuaiji' specified in the ON DUPLICATE KEY UPDATE clause is updated.

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