Insert Into a SQL Table or Update if the Row Exists

1. Overview

In this tutorial, we’ll explore inserting a row into a SQL table or updating the row if it already exists. This is a common requirement in many applications where we must insert new data or update existing data based on certain conditions.

Since records usually have a unique identifier, we cannot insert a new record with the same identifier as an existing record. In such cases, we need to update the existing record with the new data.

We’ll explore a few ways to achieve this using SQL queries.

2. Data Setup

To demonstrate the examples, let’s consider the Department table from the Baeldung University schema.

Let’s add a row to the table:

INSERT INTO Department (id, name, code) VALUES (1, 'Computer Science', 'CS');

We’ll look at ways to update records in this table with the same id as the above.

3. MERGE Statement

The MERGE statement in SQL can insert a new record into a table or update the existing record if it already exists. It is supported by databases like Oracle, SQL Server, and PostgreSQL.

Let’s look at how we can use the MERGE statement:

MERGE INTO Department AS target
USING (SELECT 1 AS id, 'Computer Science' AS name, 'CSE' AS code) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET name = source.name, code = source.code
WHEN NOT MATCHED THEN
    INSERT (id, name, code) VALUES (source.id, source.name, source.code);

Here’s a breakdown of the MERGE statement:

• MERGE INTO Department AS target: Specifies the target table where we want to insert or update the record.
• USING (SELECT …) AS source: Specifies the data we want to insert into the target table.
• ON target.id = source.id: Checks if a record with the same id already exists in the target table.
• WHEN MATCHED THEN: If a match is found, we UPDATE the existing record with the new data.
• WHEN NOT MATCHED THEN: If no match is found, we INSERT a new record into the table.

When we run the above statement, the data is updated. We can then verify the data in the table using the below query:

SELECT * FROM Department;
id | name             | code
---|------------------|-----
1  | Computer Science | CSE

4. MySQL Options

MySQL provides two ways to insert a row into a table or update the row if it already exists. We can use the INSERT INTO … ON DUPLICATE KEY UPDATE syntax or the REPLACE statement.

4.1. Using the REPLACE Statement

The REPLACE statement in MySQL inserts a new row into the table. If the row already exists, it deletes the existing row and inserts the new row. Let’s see how we can use the REPLACE statement to insert a new record into the Department table:

REPLACE INTO Department (id, name, code) VALUES (1, 'Computer Science', 'CSE');

This is simple to understand and use, but it has its drawbacks. When the existing row is deleted and a new row is inserted, there could be side effects if the table is used in foreign key constraints or triggers. It may also update the auto-increment value of the primary key. Additionally, it’s not efficient as it deletes and reinserts the row, which can be slower than updating the existing row.

4.2. Using INSERT INTO … ON DUPLICATE KEY UPDATE

The INSERT INTO … ON DUPLICATE KEY UPDATE syntax in MySQL allows us to specify an action when duplicate data is found. If the row already exists, it runs the UPDATE statement on the existing row.

Let’s see how we can use this syntax to insert or update a new record into the Department table:

INSERT INTO Department (id, name, code) VALUES (1, 'Computer Science', 'CSE')
ON DUPLICATE KEY UPDATE name = 'Computer Science', code = 'CSE';

This syntax is more efficient than the REPLACE statement as it updates the existing row instead of deleting and reinserting it. It also preserves the auto-increment value of the primary key and maintains foreign key constraints and triggers. Additionally, here we can specify the columns to update in the ON DUPLICATE KEY UPDATE clause. For example, we may want to update only the code column if the row already exists:

INSERT INTO Department (id, name, code) VALUES (1, 'Computer Science', 'CSE')
ON DUPLICATE KEY UPDATE code = 'CSE';

5. INSERT INTO … ON CONFLICT DO UPDATE (PostgreSQL)

PostgreSQL provides the INSERT INTO … ON CONFLICT DO UPDATE syntax to insert a new row into a table or update the row if it already exists. This syntax is similar to the INSERT INTO … ON DUPLICATE KEY UPDATE syntax in MySQL:

INSERT INTO Department (id, name, code) VALUES (1, 'Computer Science', 'CSE')
ON CONFLICT (id) DO UPDATE SET name = 'Computer Science', code = 'CSE';

6. Conclusion

In this article, we explored different ways to insert a row into an SQL table or update the row if it already exists. We looked at using the MERGE statement in SQL, the REPLACE statement in MySQL, and the INSERT INTO … ON DUPLICATE KEY UPDATE syntax in MySQL. We also discussed the INSERT INTO … ON CONFLICT DO UPDATE syntax in PostgreSQL.

Each of these methods has its use cases and considerations. It’s important to choose the right method based on the requirements of the application.