How to Handle Unique Key Constraints When Inserting or Updating in MySQL?

Insert or update using unique key constraints in MySQL

Inserting or updating rows in a MySQL table can be tricky when you need to deal with duplicate key constraints. To solve this problem, MySQL provides the versatile "INSERT ... ON DUPLICATE KEY UPDATE" statement.

As stated in the question, you want to insert a row into a table with a unique key. However, if a row with the same unique key already exists, you want to update its value.

Traditionally, inserting a row would violate a unique key constraint, resulting in an error message. "INSERT IGNORE" suppresses errors but does not perform an update operation.

Solution: INSERT ... ON DUPLICATE KEY UPDATE

The solution lies in using the "INSERT ... ON DUPLICATE KEY UPDATE" syntax. Let's break this query down using the example provided:

<code class="language-sql">INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
NAME="A", AGE=19</code>

• INSERT INTO: If no duplicate keys exist, inserts a new row into the specified table ("table_name").
• (ID, NAME, AGE): Specifies the columns to be filled in the new row.
• VALUES(1, "A", 19): Provides the values ​​to be inserted.
• ON DUPLICATE KEY UPDATE: Key part. If a row with the same value for the unique key (in this case "ID") already exists in the table, the update operation is triggered.
• NAME="A", AGE=19: Specifies the field to be updated and its new value.

Instructions:

If the row with ID=1 does not exist in the table, a new row will be inserted using the provided value. However, if a row with ID=1 already exists, only the "NAME" and "AGE" columns will be updated with the specified values, and the "ID" will remain unchanged.

The above is the detailed content of How to Handle Unique Key Constraints When Inserting or Updating in MySQL?. For more information, please follow other related articles on the PHP Chinese website!