How to Update Record if It Exists Else Insert It in the MySQL Table

The database managers may occasionally need to add a record to the MySQL table or update one if it is regularly present. MySQL will send an alert if a user tries inserting a record that already exists or has a duplicate PRIMARY KEY value.

This article outlines the methods that can be used to correct this mistake and get a better result.

Update Record if It Exists Else Insert It in the MySQL Table

When it’s required to insert rows after determining if they are, in fact, new or already exist, MySQL offers several helpful statements.

Use REPLACE INTO to Update the Record if It Exists Else Insert It in the MySQL Table

One approach is to use the REPLACE statement if you want to truly replace rows when the INSERT commands would fail due to duplicate UNIQUE or PRIMARY KEY values as described previously.

There are two possible consequences for each issued command when using a REPLACE INTO statement:

1. A typical INSERT statement is executed because no data row with the desired values could be discovered.
2. When a matched data row is discovered, the existing row is erased using the usual DELETE statement, and the standard INSERT is then carried out.

For instance, assume we have an employees table with columns for id, department, employee_name, and joining_year. We’ve made the decision to change our department and joining_year of id = 101 record back to the original Operations record.

-- 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 browser compatible with MySQL. It will display the following outcome:

Output:

-- 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 outcome shows that two rows were impacted because we DELETED the previous record and then INSERTED the new row to take its place.

Use INSERT ... ON DUPLICATE KEY UPDATE to Update the Record if It Exists Else Insert It in the MySQL Table

We can employ the INSERT ... ON DUPLICATE KEY UPDATE statement and clause as an alternative approach for inserting into rows that might have duplicate UNIQUE or PRIMARY KEY values.

By only issuing INSERT or UPDATE instructions and never DELETE, utilizing INSERT... ON DUPLICATE KEY UPDATE is non-destructive, in contrast to REPLACE, which is inherently harmful due to the DELETE commands it executes when necessary.

For instance, assume we have an employees table with columns for id, department, employee_name, and joining_year. We’ve made the decision to change our department and joining_year of id = 101 record back to the original Operations record.

The new ON DUPLICATE KEY UPDATE clause can now be added 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 above example, we’ve chosen to use user variables to avoid declaring the actual values we wish to INSERT or UPDATE more than once, even though it is not necessary for the ON DUPLICATE KEY UPDATE method to work as intended.

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

Output:

-- 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     |
+----+------------+---------------+----------------+