Imagine you’re adding records to your MySQL table—but sometimes the row exists, and you need to update instead of insert. That’s when MySQL’s UPSERT pattern comes in handy. Also known as ‘INSERT … ON DUPLICATE KEY UPDATE’, UPSERT lets you insert new data or update existing rows in one atomic operation. In this tutorial, you’ll learn many ways to perform UPSERT in MySQL and to use each—easily and error‑free.
What is UPSERT in MySQL?
UPSERT is not a keyword in MySQL but a special way to add new data or change old data with one command. It means when data is to be saved, if it is new, UPSERT adds a new row. If the table already has that record, it will get updated without making an extra entry.
Since MySQL lacks the UPSERT keyword, you have to write a query that says: insert these values, but if it is duplicate by unique key, then update the old data. The way to support is to use MySQL “ON DUPLICATE KEY UPDATE” clause. It is the most common way to perform the UPSERT. The query would look like the below:
INSERT INTO table_name (columns...)
VALUES (values...)
ON DUPLICATE KEY UPDATE column = new_value;MySQL UPSERT Examples
Example:1 You have a customers table with unique email for each person. You want to add new customer or update phone number. You write:
INSERT INTO customers (email, name, phone)
VALUES ('maria.fernandez@gmail.com', 'Maria Fernandez', '555-6677')
ON DUPLICATE KEY UPDATE phone = '555-6677';This command adds Maria if her email is not in the table yet. But if Maria’s email is already there, it changes her phone number to new one.
Example:2 Consider a product list where each product has a unique code. You want to add new products or change price for old products. UPSERT helps:
INSERT INTO products (product_code, product_name, price)
VALUES ('B100', 'Wireless Earphones', 29.99)
ON DUPLICATE KEY UPDATE price = 29.99;This inserts product if new or updates price if product code exists.
Why UPSERT is Useful?
UPSERT is found to be useful because it saves time and code. Without UPSERT, you must check if data exists first, then insert or update. UPSERT does both with one command.
Remember, UPSERT works only if your table has a unique key like ID, email, or product code. This key helps MySQL know if data is duplicate.
UPSERT Using MySQL INSERT IF NOT EXISTS
One easy way to UPSERT in MySQL is by using INSERT IF NOT EXISTS. This adds new data if the same is not already in the table. If the record is there, it won’t change anything.
For example, imagine you have a subscribers table where every email must be unique. Each person is unique by their email. You want to add a new subscriber only if their email is not already saved. Here’s how you do it:
INSERT INTO subscribers (email, name)
SELECT 'lucas.brown@example.com', 'Lucas Brown'
WHERE NOT EXISTS (
SELECT 1 FROM subscribers WHERE email = 'lucas.brown@example.com'
);This query will add Lucas only when his email id is not found in the table. If it’s already there, nothing happens.
This method is good when you want to prevent duplicates. But if your goal is to update info when the record exists, you need a different UPSERT way, like ON DUPLICATE KEY UPDATE.
UPSERT with ON DUPLICATE KEY UPDATE
This method performs the UPSERT using the ON DUPLICATE KEY UPDATE. For this to work, the table must have either of the two – a primary key or a unique index. MySQL checks for the exiting data based on this unique value. If found, it updates the existing record. If not, a new row is added.
MySQL UPSERT Example:
Let’s say you have a table of products which has product_code field with unique constraint. Now, to add a new row or update its price if it exists, you can use:
INSERT INTO products (product_code, product_name, price)
VALUES ('P1001', 'Smartphone', 499.99)
ON DUPLICATE KEY UPDATE price = 499.99;This query does the following:
- If P1001 already exists, the price will be updated.
- If not, a new product will be added.
This method has a requirement of a unique keys (like product_code), and it’s the most straightforward way to handle UPSERT in MySQL.
MySQL UPSERT without a Primary Key
MySQL UPSERT without a primary key is possible. But, it will work only if the table has another field or a group of columns that together form a unique key. You can use that as an alternative to search the table for duplicate records.
MySQL UPSERT Example:
In some cases, our table may not have the primary key column. For example, our project has a table called empl_rec with two fields: email and empl_id. They are not set as the primary key. But you can get a unique combination by using them as a group. Now, we’ll run the UPSERT operation to update the phone no of an employee without a primary key.
INSERT INTO empl_rec (email, empl_id, name, phone)
VALUES ('vikram.bhatt@example.com', 'E2794', 'Vikram Bhatt', '438-307')
ON DUPLICATE KEY UPDATE phone = '438-307';How It Works:
- The email and empl_id fields together formed a unique key that helps to avoid duplicate data.
- If Vikram’s email or empl_id were already present in the table, the above query will update his phone number.
- When no similar record happens, the UPSERT will add a new row with his details.
Let’s see each of the above in action below.
UPSERT Using INSERT IGNORE
When you use INSERT IGNORE for UPSERT, it runs without raising errors, even if a duplicate entry is found. If the target table already contains a row with a matching UNIQUE or PRIMARY key, MySQL will simply skip adding the duplicate row.
However, this will also skip the INSERT operation altogether. Such kind of statement is useful when we need to add a large number of records in one go. And, the table may already have a part of that data inside. Let’s test this behaviour with the help of an example. But before that, you may have a quick look at this diagram.
Example:1 – Stock Market Data Feed
In stock market databases, every stock symbol must be unique. When live data comes, it may send the same stock again along with new ones. A plain INSERT would throw an error and stop the batch, but INSERT IGNORE allows the process to continue.
CREATE TABLE stock_prices (
symbol VARCHAR(10) PRIMARY KEY,
price DECIMAL(10,2)
);
INSERT INTO stock_prices VALUES ('TCS', 3450.75);
INSERT IGNORE INTO stock_prices VALUES
('TCS', 3450.75),
('INFY', 1560.10);
Here the duplicate TCS row is ignored, but the INFY is inserted successfully. This way new data keeps flowing without interruption.
Example:2 – Insurance Policy Records
In insurance, each policy ID must be unique. When data from multiple branches is merged, the same policy may appear more than once. If we use normal INSERT, the duplicate will block the insert of other valid policies.
CREATE TABLE policies (
policy_id INT PRIMARY KEY,
customer_name VARCHAR(50)
);
INSERT INTO policies VALUES (101, 'Rahul');
INSERT IGNORE INTO policies VALUES
(101, 'Rahul'),
(102, 'Meena'),
(103, 'Karan');
MySQL UPSERT Example – Employee Email Update
Here the duplicate record for policy 101 is skipped, while new policies 102 and 103 are added. This prevents errors and ensures all fresh policies are recorded.
Meanwhile, you are here, try checking out our tutorial on MySQL INSERT statement.
UPSERT Using REPLACE INTO
There come situations when we have to replace some rows even if INSERT could fail due to duplicate values of the primary key field. Hence, we should use the REPLACE statement for such cases.
However, if we opt to use REPLACE, then it could result in one of the following outcomes:
- If we don’t face any error, then REPLACE would behave as a regular INSERT command.
- If a duplicate record exists, then REPLACE would first delete it and perform the INSERT subsequently.
Here is a diagram showing the flow of steps for UPSERT using REPLACE INTO.
Think like this. You are keeping employee records in a table. Each employee has a unique ID. Now one day you get fresh list from HR. It has same employees but some of them changed their email. If you try normal INSERT, MySQL will not allow because that ID already exist. Error will stop the insert.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50)
);
INSERT INTO employees VALUES (101, 'Meenakshi', 'meenakshi@oldmail.com');Now HR sends new data with Meenakshi’s updated email.
REPLACE INTO employees VALUES (101, 'Meenakshi', 'meenakshi@newmail.com');What happens here is simple. MySQL sees duplicate ID. It deletes the old row and puts the new one. End result is only one record for Meenakshi, but with his new email.
emp_id | name | email
------------------------------
101 | Meenakshi | meenakshi@newmail.com👉 Why useful?
This is like telling MySQL: “I don’t care if the record already there, just throw old one and keep this new one.” It is very handy in real life when data keeps changing, like employee contacts, product prices, or customer addresses.
👉 Interview Tip:
Many interviewers ask: “How INSERT IGNORE different from REPLACE?”
Answer in simple words:
INSERT IGNORE→ skip duplicate.REPLACE→ delete old, add new.
We’ve reached the end of this tutorial. If still curious about this topic, you should read: What is the difference between UPSERT and insert in MySQL?
MySQL UPSERT: What You Need to Remember
UPSERT in MySQL helps you add new data or update old data with one easy command. Using MySQL UPSERT examples like ON DUPLICATE KEY UPDATE or REPLACE INTO lets you manage your data fast without duplicates. Even if you don’t have a primary key, MySQL UPSERT without primary key methods can help. You can also use MySQL insert if not exists or MySQL UPSERT with WHERE clause for more control. This idea is similar to MSSQL UPSERT and MySQL MERGE logic, which also combine insert and update actions. Learning these techniques saves time and keeps your database clean.
Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.
