In databases, sometimes you want to add new information, but if they’re already there, you might need to update their information. We use the term “upsert” for this dual action of inserting new records or updating existing ones.

MySQL provides us with some simple ways to handle this. In this article, we are going to explore different approaches to inserting it into a MySQL table or update if exists.

Insert into a MySQL Table or Update if Exists

Firstly, Let’s try a common scenario where a user attempts to register using the traditional insert method. and here is the existing dataset in the registered_user table.

dataset in the registered_user table

Now let’s try to insert the row with id = 4, and as you can see in the above images, this already exists.

Using the classic insert statement

We are getting an error because the specified user id (4) already exists as a primary key in the registered_user table. The developer receives an error message indicating a key violation or duplicate entry.

error encountered

This situation highlights the common challenge of dealing with existing data when attempting to insert new records. To overcome this, you can explore the mentioned approaches.

• Using On Duplicate Key Update
• Using Replace Into
• Using Ignore

Using On Duplicate Key Update

This classic method involves trying to insert data first. If a similar entry already exists, it smartly updates the existing one. Just like managing your contacts, try adding a new one, but if they’re already there, make sure their details are up-to-date.

Syntax:

— Try to insert a new record

INSERT INTO your_table (column1, column2, …)

VALUES (value1, value2, …)

— If there’s a duplicate, update the existing record

ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, …;

Example: ON DUPLICATE KEY

Let’s see the result with an example, this will update the user if exists with id=1.

-- Using traditional insert, then update if exists
INSERT INTO registered_users (id, name, email, username, password)
VALUES (1, 'Geek', '[email protected]', 'geek123', 'hashed_password')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email), password = VALUES(password);

Output:

As a result, the rows will be affected and data will updated

now you can see the updated table dataset

Explanation: In this MySQL example, we can see that we can easily insert or update the user data in the “registered_users” table. If the user has ID=1, we can update the name, email, and password; if not, we can simply insert a new record.

Using REPLACE INTO

Simplify your upserts with the REPLACE INTO statement. It attempts to insert a new record and, if needed, smoothly replaces the existing one.

Syntax:

— Add a new record or replace the existing one

REPLACE INTO your_table (column1, column2, …)VALUES (value1, value2, …);

Example: REPLACE INTO

Let’s see another example this will update the data of id=2 if data exists.

-- Using replace into for a simpler upsert
REPLACE INTO registered_users (id, name, email, username, password)
VALUES (2, 'Jane Smith', '[email protected]', 'jane_smith', 'hashed_password');

Output:

Updated registered_user table

Explanation: The REPLACE INTO statement in this MySQL example makes it easy to update data in the “registered_users” table. If ID=2, it will update the data. Otherwise, it will insert a new record.

Using IGNORE INTO

This method keeps things straightforward with INSERT IGNORE. It tries to add a new record and, if there’s a duplicate, simply ignores it.

Syntax:

— Try to add a new record, but ignore if there’s a duplicate

INSERT IGNORE INTO your_table (column1, column2, …)

VALUES (value1, value2, …);

Example: IGNORE INTO

Let’s try this with another example.

 -- Using insert or ignore for a simple upsert
INSERT IGNORE INTO registered_users (id, name, email, username, password)
VALUES (3, 'Bob Johnson', '[email protected]', 'bob_johnson', 'hashed_password');

Output: As the data exists with id=3, So, this will not affect any rows and ignore the data insertion and the table will remain the same.

registred_user table

Explanation: The INSERT IGNORE INTO statement in this MySQL example makes it easy to perform an upsert operation on the “registered_users” table. If id=3 is present in the table, it will ignore the new data. Otherwise, it will insert a new record into the table.

Conclusion

In conclusion, When it comes to MySQL tables, you have the option of inserting new data or updating existing records. You can easily manage this by using techniques such as ON DUPLICATE KEY UPDATE or REPLACE INTO. These methods provide you with more flexibility and efficiency. You can easily simplify data updates based on the existence or absence of certain identifiers.

FAQs on How to INSERT If Row Does Not Exist in MySQL

What is the difference between ON DUPLICATE KEY UPDATE and REPLACE INTO?

ON DUPLICATE KEY UPDATE updates existing records if a duplicate key is found, whereas REPLACE INTO deletes the existing record and inserts the new one.

When should I use INSERT IGNORE?

Use INSERT IGNORE when you want to insert new records without affecting existing records that would cause a duplicate key error.

Does REPLACE INTO delete the existing record?

Yes, REPLACE INTO deletes the existing record with the same primary key or unique index and then inserts the new record.

Can I use ON DUPLICATE KEY UPDATE with multiple columns?

Yes, you can specify multiple columns to update in the ON DUPLICATE KEY UPDATE clause.