JIYIK CN >

A database administrator may occasionally need to add a record to a MySQL table, or update a frequently occurring record. MySQL will send an alert if a user attempts to insert a record that already exists or has a duplicate PRIMARY KEY value.

This article outlines methods you can use to correct this error and achieve better results.

If it exists then update the record, otherwise insert it into the MySQL table

MySQL provides several useful statements when you need to insert a row after determining whether the row is actually new or already exists.

If the record exists then update the record using REPLACE INTO else insert it into MySQL table

If INSERTthe command fails due to duplicate UNIQUEor PRIMARY KEYvalues, as mentioned earlier, if you want to actually replace the row, one way is to use the REPLACE statement.

When you use the REPLACE INTO statement, each command issued can have two possible results:

• A typical INSERT statement is executed because no row with the required value can be found.
• When a matching row is found, the existing row is deleted using the usual DELETE statement, and then a standard INSERT is performed.

For example, suppose we have an employees table with id, department, employee_name, and joining_year columns. We have decided to change our department and joining_year of id = 101records back to the original operational records.

-- Before update
SELECT * from employees where id = 101;
REPLACE INTO employees
    (id, department, employee_name, joining_year)
VALUES
    (101, 'Operations', 'John Doe', 2013);
-- After update
SELECT * from employees where id = 101;

Run the above line of code in any MySQL compatible browser. It will display the following result:

-- Before update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Technology | John Doe      |       2017     |
+----+------------+---------------+----------------+

Query OK, 2 rows affected (0.01 sec)

-- After update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Operations | John Doe      |       2013     |
+----+------------+---------------+----------------+

In this example, even though we only changed one item, the results show that two rows were affected because we deleted the previous record and then inserted the new row to replace it.

Use INSERT ... ON DUPLICATE KEY UPDATE to update the record if it exists otherwise insert it into the MySQL table

We can use INSERT ... ON DUPLICATE KEY UPDATEthe INSERT statement and the VALUES clause as an alternative to inserting into rows that might have duplicate UNIQUE or PRIMARY KEY values.

Using INSERT ... ON DUPLICATE KEY UPDATE is nondestructive by issuing only INSERT or UPDATE instructions and not DELETEs, compared to REPLACE, which is inherently harmful because it executes a DELETE command when necessary.

For example, suppose we have an employees table with id, department, employee_name and joining_year columns. We have decided to change our department and joining_year of id = 101 record back to the original operation record.

Now we can add the new ON DUPLICATE KEY UPDATE clause to our original INSERT statement:

-- Before update
SELECT * from employees where id = 101;
SET @id = 101,
    @department = 'Operations',
    @employee_name = 'John Doe',
    @joining_year = 2013;
INSERT INTO employees
    (id, department, employee_name, joining_year)
VALUES
    (@id, @department, @employee_name, @joining_year)
ON DUPLICATE KEY UPDATE
    department = @department,
    employee_name = @employee_name,
    joining_year = @joining_year;
-- After update
SELECT * from employees where id = 101;

In the example above, we chose to use user variables to avoid declaring multiple times the actual value we wish to INSERT or UPDATE, even though ON DUPLICATE KEY UPDATEit is not necessary for the method to work as expected.

Run the above line of code in any browser compatible with MySQL. It will display the following result:

-- Before update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Technology | John Doe      |       2017     |
+----+------------+---------------+----------------+

Query OK, 1 rows affected (0.00 sec)

-- After update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Operations | John Doe      |       2013     |
+----+------------+---------------+----------------+