In MySQL, while working with tables, it is important to understand the difference between UPSERT and INSERT statements. The INSERT statement simply adds entirely new records to a table, whereas UPSERT combines the functionalities of both INSERT and UPDATE. UPSERT is employed when you want to insert new records into a table but also need to handle potential conflicts, updating existing records if conflicts arise. This is particularly useful in scenarios where maintaining data integrity is crucial.
Differences Between UPSERT and INSERT
| Criteria | INSERT | UPSERT |
|---|---|---|
| Primary Purpose | Adds entirely new records to a table. | Inserts new records or updates existing ones to maintain data integrity. |
| Handling Duplicates | Does not inherently handle duplicate entries; results in an error if conflicts occur. | Specifically designed to handle duplicate entries by updating existing records. |
| Syntax | Follows a straightforward syntax, specifying values for each column in the new record. | Uses the INSERT ... ON DUPLICATE KEY UPDATE syntax for conditional updates based on key conflicts. |
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.
Illustrate the Difference Between Upsert and Insert with Examples
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 a user might attempt to change their password, ensuring that the information is updated if the username or email already exists.
INSERT Examples
#1 Consider a table employees where each employee has a unique employee ID. If you’re hiring a new employee and want to add them to the table, 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 recording financial transactions with a unique transaction ID. When logging a new transaction, you don’t need to worry about conflicts, as each transaction ID is unique. Therefore, an INSERT statement suffices:
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.
Examples of when to use UPSERT
You’ll use the upsert call in the following scenario.
- If you need to ensure that data is always up-to-date. For example, you could use UPSERT to update a user’s email address in a database when they register for a new account, or to update the inventory count of a product when a purchase is made.
- When you need to prevent duplicate data from being inserted into a database. For example, use UPSERT to insert a new product into a product catalog table, but only if it does not already exist.
- Use it when you have to run a single operation on a row, even if the row does not exist. For example, you could use UPSERT to increment the view count of a video, regardless of whether or not the video has been viewed before.
Examples of when to use INSERT
Here are some basic examples where you should use the insert operation.
- In case, you are sure that the row you are inserting does not already exist in the database. For example, you could use INSERT to append a new user into a database when he registers for a new account.
- When you need to insert multiple rows into a database at once. For example, you could use INSERT to insert a batch of new product data into a product catalog table.
- Use insert to add a row to the table without any updates being applied. For example, use INSERT to insert a new log entry into a database.
Conclusion
UPSERT and INSERT are both useful database operations, but they have different purposes. UPSERT is used to insert or update rows in a table in a single statement, while INSERT is used to insert new rows into a table.
Which operation you choose to use will depend on the specific requirements of your application.
Happy learning!
