How to INSERT If Row Does Not Exist in MySQL

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

Insert or Update into MySQL Table: 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', 'Geek@gmail.com', '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.

Insert or Update into MySQL Table: 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 oneREPLACE 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', 'jane@example.com', '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.

Insert or Update into MySQL Table: Using IGNORE INTO

This method keep 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', 'bob@example.com', 'bob_johnson', 'hashed_password');

Output: As the data exist with id=3, So, this will not affect any rows and ignore the data insertion and table will remain 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.