The data insert of the volatile table is as follows.
When inputting data in a volatile table, data with duplicate primary key values can be updated using the ON DUPLICATE KEY UPDATE clause.
Update Data Value to be Inserted
If the INSERT statement specifies data to be inserted, but there is other data that matches the primary key value of the insert data, the INSERT statement fails and the corresponding data is not inserted. If there is another data that matches the primary key value of the insertion data, and if you wish to update the corresponding data instead of insertion, a
ON DUPLICATE KEY UPDATE clause can be added.
If there is no duplicate primary key data, the contents of the data to be inserted are inserted as is.
If there is duplicate primary key data, the existing data is updated with the contents of the data to be inserted.
The constraints for using this function are as follows.
The primary key must be specified in the volatile table.
The value to be inserted must include the primary key value.
Specify Data Value to be Updated
Similar to above, but if you need to update to a different column value than the data value to be inserted, it can be specified through the
ON DUPLICATE KEY UPDATE SET clause. The data value to be updated can be specified under the
If the primary key duplication data does not exist, the contents of the embedded data are inserted as it is.
If there is duplicate primary key data, the existing data is updated only with the update data specified in the
The primary key value can not be specified as the data value to be updated.
The values of the columns not specified in the
SETclause are not updated.