The Difference between UPSERT & Insert

In MySQL, it is important to understand the difference between UPSERT and INSERT statements while working with tables.

Understand the Difference Between UPSERT & INSERT

The INSERT statement adds entirely new records to a table, whereas UPSERT combines the functionalities of both INSERT and UPDATE. UPSERT inserts new records into a table and handles potential conflicts by updating existing records. This is particularly useful in scenarios where maintaining data integrity is crucial.

UPSERT vs. INSERT in MySQL

Here is a simple diagram to show the difference between the UPSERT and insert statements.

Next, we’ve specifically placed another picture showing how the Upsert statement works in MySQL.

Examples to Illustrate the Difference

Let’s take two examples to showcase how these two database operations work differently.

UPSERT Examples

#1 Suppose you have a table named products with a unique constraint on the product_code column. You want to insert a new product or update its price if it already exists. UPSERT is the ideal choice in this scenario:

Mysql code:

INSERT INTO products (product_code, product_name, price) VALUES ('P001', 'Widget A', 73.13) ON DUPLICATE KEY UPDATE price = 73.13;

This UPSERT statement ensures that if a product with the code ‘P001’ already exists, its price is updated; otherwise, a new record is inserted.

#2 Imagine a users table with unique constraints on both username and email. If you’re implementing user authentication and want to insert a new user or update their information (e.g., password change), UPSERT is the way to go:

Mysql code:

INSERT INTO users (username, email, password) VALUES ('hello_world', 'hello@world.com', 'new_secure_pass') ON DUPLICATE KEY UPDATE password = 'new_secure_pass';

This UPSERT statement allows you to handle scenarios where users might attempt to change their password. It ensures to update the record if the username or email already exists.

INSERT Examples

#1 Consider a table “employees” where each employee has a unique employee ID. When you are hiring a new employee, INSERT is the appropriate choice.

Mysql code:

INSERT INTO employees (employee_id, name, position) VALUES (1001, 'Hello World', 'Software Engineer');

Here, you are adding a new record for a new employee without concern for pre-existing entries, making INSERT the suitable operation.

#2 Consider a table “transactions” that records financial transactions with a unique ID. While logging a new transaction, you don’t need to worry about conflicts, as each transaction ID is unique. Therefore, an INSERT statement suffices in this case.

Mysql code:

INSERT INTO transactions (trans_id, amount, description) VALUES ('TXN123', 37.87, 'Purchase of goods');

In this case, you are simply adding a new record to the transactions table without the need for UPSERT functionality.

In summary, while INSERT is straightforward for adding new records when duplicates are not a concern, UPSERT shines in scenarios where you want to insert new data while gracefully handling conflicts and maintaining data integrity. Choosing the right operation depends on the specific requirements of your database and the nature of the data you are managing.

When to use UPSERT

You should use the upsert call in the following scenario.

When to use INSERT

Here are some cases where you should use the insert operation.

Summary

UPSERT and INSERT are useful database operations, but they have different purposes. UPSERT combines insert and update in a single statement whereas INSERT adds new rows into a table.

Which operation you choose ultimately depends on the specific requirements of your application.

We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.

Happy learning!