How to Insert or Update MySQL Rows Based on a Unique Key?

Inserting or Updating MySQL Rows Based on Unique Key

Consider the scenario where you aim to add data to a table but face the challenge of inserting new rows while updating existing ones based on the uniqueness of a specific column (datenum).

To address this requirement, you may initially attempt to utilize an UPDATE statement as exemplified in the question; however, this approach is limited to modifying existing rows only. For scenarios where the datenum value doesn't exist in the table, no action will be taken.

To accomplish both insertion and updation based on the datenum unique column, MySQL provides the INSERT ... ON DUPLICATE KEY UPDATE syntax. This syntax enables you to define an insert operation that automatically updates the row if a duplicate key is encountered.

Here's an improved version of the UPDATE statement using the INSERT ... ON DUPLICATE KEY UPDATE syntax:

INSERT INTO AggregatedData (datenum,Timestamp)
VALUES ("734152.979166667","2010-01-14 23:30:00.000")
ON DUPLICATE KEY UPDATE 
  Timestamp=VALUES(Timestamp)

In this statement, the unique key column datenum is used in the INSERT clause, while the updated value for Timestamp is specified in the ON DUPLICATE KEY UPDATE clause. This ensures that if a row with the same datenum already exists, the Timestamp column will be updated with the provided value, while other columns remain unchanged.

By employing this technique, you achieve both insertion of new rows and updation of existing rows in your MySQL table based on the unique key column.

The above is the detailed content of How to Insert or Update MySQL Rows Based on a Unique Key?. For more information, please follow other related articles on the PHP Chinese website!