MySQL INSERT vs UPDATE: Syntax, Examples, ON DUPLICATE KEY UPDATE, and REPLACE

1. Introduction

MySQL is a popular relational database management system used in many web applications and database management systems. Among its features, the INSERT statement and the UPDATE statement—used to add and modify data—play an essential role as fundamental data operations. By understanding them correctly and using them efficiently, database operations become smoother.

In this article, we provide a detailed explanation of MySQL INSERT and UPDATE, from basic usage to advanced operations. The content is suitable for beginners through intermediate users, so please use it as a reference.

2. INSERT Basics

Basic INSERT Syntax

The basic syntax of an INSERT statement is as follows.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

As an example, consider adding a new user to the users table.

INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 30);

This SQL inserts the values “Taro Yamada”, “taro@example.com”, and “30” into the name, email, and age columns of the users table, respectively.

Inserting Multiple Rows

In MySQL, you can also insert multiple rows of data at once. In that case, the syntax looks like this.

INSERT INTO users (name, email, age)
VALUES
('Hanako Sato', 'hanako@example.com', 25),
('Ichiro Suzuki', 'ichiro@example.com', 40);

Using this method reduces the number of database accesses and can improve performance.

Handling NULL Values

When using an INSERT statement, you may need to handle NULL values. For example, if age is not set, write it like this.

INSERT INTO users (name, email, age)
VALUES ('Jiro Tanaka', 'jiro@example.com', NULL);

Note that if a column has a NOT NULL constraint, inserting a NULL value will result in an error. In that case, you must set a default value or specify a value explicitly.

3. UPDATE Basics

Basic UPDATE Syntax

The UPDATE statement is used to modify data in existing records. In this section, we explain the basic syntax, how to update with conditions, and why the WHERE clause is important.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

As an example, consider updating the age of a specific user in the users table.

UPDATE users
SET age = 35
WHERE name = 'Taro Yamada';

This SQL updates the age to 35 for the user whose name is “Taro Yamada” in the users table.

Why the WHERE Clause Matters

If you omit the WHERE clause in an UPDATE statement, all rows in the table will be updated. This can cause unintended data loss, so be sure to specify conditions.

-- When the WHERE clause is omitted
UPDATE users
SET age = 30;

This SQL sets the age of all users to 30.

Conditional Updates

When specifying multiple conditions, use AND or OR.

UPDATE users
SET age = 28
WHERE name = 'Hanako Sato' AND email = 'hanako@example.com';

In this way, you can update data with more precise conditions.

4. Combining INSERT and UPDATE

In database operations, you may encounter situations where you sometimes need to add new data and other times update existing data. In such scenarios, you can process efficiently using INSERT ... ON DUPLICATE KEY UPDATE or the REPLACE statement. In this section, we explain how to use each and what to watch out for.

How to Use INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE is effective when primary key or unique key constraints exist. With this syntax, you can perform “update if it exists, insert if it doesn’t” in a single SQL statement.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = new_value1, column2 = new_value2, ...;

Example

Consider adding a new user to the users table. If the same email already exists, update that user’s name and age.

INSERT INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age);

This SQL statement does the following:

1. If no record exists with email = 'taro@example.com', insert the data.
2. If an existing record does exist, update name and age.

Notes

• If there is an AUTO_INCREMENT column, the counter increases even when a duplicate key occurs. This can cause unintended behavior, so be careful.
• By using the VALUES() function, you can reuse the values you attempted to insert for the update.

How REPLACE Works and How It Differs

The REPLACE statement completely deletes existing data (when a duplicate key is found) and then inserts the new data. Unlike INSERT ... ON DUPLICATE KEY UPDATE, the original record is deleted.

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

Insert data into the users table, and if email is duplicated, delete the existing data and insert the new data.

REPLACE INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30);

This SQL statement does the following:

1. If a record exists with email = 'taro@example.com', delete that record.
2. Insert the new data.

Notes

• Because it performs delete + insert, it may affect triggers and foreign key constraints.
• Be aware of side effects from deletion (such as losing related data).

Performance Considerations

INSERT ... ON DUPLICATE KEY UPDATE and REPLACE each have pros and cons. For large databases or high-frequency operations, performance differences matter, so consider the following points.

Choosing the Right Use Case

• When INSERT … ON DUPLICATE KEY UPDATE is suitable
• When foreign key constraints or triggers exist and you want to avoid deletes.
• When updates occur frequently.
• When REPLACE is suitable
• When you need a complete replacement of the data.
• For simple tables not affected by foreign key constraints or triggers.

5. Practical Examples

Here, we introduce real-world use cases for MySQL INSERT and UPDATE, as well as practical usage of “INSERT … ON DUPLICATE KEY UPDATE” and “REPLACE.” This will help you understand how to apply what you have learned in actual work.

Use Case 1: Inventory Management System

In an inventory management system, product registration and stock updates happen frequently. Use INSERT to add new products, and use UPDATE or “INSERT … ON DUPLICATE KEY UPDATE” to update existing products.

Inserting and Updating Product Data

For example, assume a product table products is structured as follows.

Registering a New Product

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50);

Updating Stock Quantity (Existing Product)

UPDATE products
SET stock = stock + 20
WHERE product_id = 1;

Insert New or Update Stock Quantity

If you want to either register a new product or update stock for an existing product, use “INSERT … ON DUPLICATE KEY UPDATE.”

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

This SQL accomplishes the following:

• If no data exists for product ID 1, insert it.
• If data exists for product ID 1, add 50 to the stock quantity.

Use Case 2: User Information Management

In web applications, user registration and updates are routine. Use INSERT to register new users, and use UPDATE or “INSERT … ON DUPLICATE KEY UPDATE” to update existing user information.

User Table Structure

Registering a New User

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW());

Updating User Information

For example, when a user changes their profile.

UPDATE users
SET name = 'Hanako Yamada', email = 'hanako@example.com'
WHERE user_id = 1;

Register or Update Information

When a user logs in for the first time, register them; if the user already exists, update the last login timestamp.

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW())
ON DUPLICATE KEY UPDATE
last_login = NOW();

Use Case 3: Periodic Data Updates

When dealing with sensor or log data, new data may be inserted every minute or second. In this case, you either insert new data with INSERT or update existing data conditionally.

Inserting Log Data

Here is an example table sensor_logs for recording sensor data.

Recording New Sensor Data

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW());

Update or Insert Data

If the sensor ID already exists, update the data; otherwise, insert it.

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW())
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
last_updated = VALUES(last_updated);

Notes and Best Practices

1. Error handling:
   When using ON DUPLICATE KEY UPDATE or REPLACE, it’s important to check the impact of triggers and foreign key constraints in advance.
2. Performance optimization:
   For large-scale data, use index design and transactions to operate efficiently.
3. Data integrity:
   Especially with REPLACE, delete + insert occurs, so you need measures to avoid the risk of losing related data.

6. Common Errors and How to Fix Them

When using MySQL INSERT and UPDATE statements, various errors can occur. In this section, we explain common errors, their causes, and specific ways to resolve them.

Common Error Examples

1. Duplicate Entry Error

Error message:

Error: Duplicate entry '1' for key 'PRIMARY'

Cause:

• This occurs when you try to insert a value that already exists into a column with a primary key or unique constraint (UNIQUE).

How to fix:

• Use ON DUPLICATE KEY UPDATE:
  Perform an update when a duplicate entry exists.

INSERT INTO users (user_id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);

• Check existence before inserting:
  To prevent duplicates, check whether the data already exists in advance.

SELECT COUNT(*) FROM users WHERE user_id = 1;

2. Foreign Key Constraint Error

Error message:

Error: Cannot add or update a child row: a foreign key constraint fails

Cause:

• This occurs when the referenced parent table data does not exist due to a foreign key constraint (FOREIGN KEY).

How to fix:

• Insert the related data into the parent table.

INSERT INTO parent_table (id, name) VALUES (1, 'Parent data');

• Disable foreign key constraints temporarily (not recommended).

SET FOREIGN_KEY_CHECKS = 0;
-- Data operations
SET FOREIGN_KEY_CHECKS = 1;

3. NULL-Related Error

Error message:

Error: Column 'name' cannot be null

Cause:

• This occurs when you attempt to insert a NULL value even though the column has a NOT NULL constraint.

How to fix:

• Set a default value.

ALTER TABLE users MODIFY name VARCHAR(255) NOT NULL DEFAULT 'Unspecified';

• Insert an appropriate value with INSERT.

INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', NULL);

4. Data Type Error

Error message:

Error: Data truncated for column 'age' at row 1

Cause:

• This occurs when you try to insert or update a value that does not match the column’s data type.

How to fix:

• Check the data type and use an appropriate value.

INSERT INTO users (age) VALUES (30); -- For an INT column

• Change the column data type (if needed).

ALTER TABLE users MODIFY age VARCHAR(10);

5. Table Lock-Related Error

Error message:

Error: Lock wait timeout exceeded; try restarting transaction

Cause:

• This occurs when another transaction locks the table and the lock is not released within a certain time.

How to fix:

• To avoid transaction contention, consider actions such as:
• Split queries to reduce table locking.
• Create appropriate indexes to speed up query execution.

Best Practices for Performance and Error Prevention

1. Use transaction management

• When performing multiple INSERTs and UPDATEs, use transactions to manage operations reliably.

START TRANSACTION;
INSERT INTO orders (order_id, user_id) VALUES (1, 1);
UPDATE users SET last_order = NOW() WHERE user_id = 1;
COMMIT;

2. Optimize indexes

• Setting appropriate indexes on primary keys and foreign keys reduces error risk and improves performance.

ALTER TABLE users ADD INDEX (email);

3. Rollback on errors

• When an error occurs, perform a rollback to maintain data integrity.

START TRANSACTION;
-- Some operations
ROLLBACK; -- On error

7. FAQ

When using MySQL INSERT and UPDATE statements, many people have similar questions. In this section, we deepen understanding through common questions and answers.

Q1: Which should I use, INSERT or UPDATE?

Answer:

Use INSERT to add new data, and use UPDATE to modify existing data. However, if adding new data and updating existing data are mixed, using “INSERT … ON DUPLICATE KEY UPDATE” is the best option.

Example:

INSERT INTO users (user_id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);

This syntax can handle inserting new data and updating existing data in a single query.

Q2: Can I use ON DUPLICATE KEY UPDATE for every use case?

Answer:

No. ON DUPLICATE KEY UPDATE has the following limitations:

1. It works only when a primary key or unique key is defined. If none is defined, no error occurs, but it won’t behave as intended.
2. For large-scale updates, performance may degrade. In that case, consider using transactions or splitting data.

Q3: What’s the difference between REPLACE and ON DUPLICATE KEY UPDATE?

Answer:

They are similar, but their behavior differs significantly.

As a guideline, use ON DUPLICATE KEY UPDATE when you want to update without deleting data, and use REPLACE when you want a complete replacement.

Q4: What happens if I forget the WHERE clause?

Answer:

If you run an UPDATE statement without specifying a WHERE clause, all records in the table will be updated. This is extremely dangerous and can cause unintended data changes.

Example:

-- The age of all records is updated to 30
UPDATE users
SET age = 30;

Prevention:

• Always specify a WHERE clause so only data matching specific conditions is updated.
• As a best practice, run a SELECT statement first to confirm the target data before executing the UPDATE.

SELECT * FROM users WHERE name = 'Taro Yamada';
UPDATE users SET age = 35 WHERE name = 'Taro Yamada';

Q5: Is there a way to speed up INSERT and UPDATE?

Answer:

You can improve performance using the following methods.

1. Optimize indexes:
   Set appropriate indexes on required columns to speed up search and update operations.

CREATE INDEX idx_email ON users(email);

2. Batch operations:
   It’s more efficient to insert or update multiple rows at once than to process one row at a time.

INSERT INTO users (name, email, age)
VALUES
('Hanako Sato', 'hanako@example.com', 25),
('Ichiro Suzuki', 'ichiro@example.com', 40);

3. Use transactions:
   Processing multiple operations in a single transaction reduces lock contention.

START TRANSACTION;
INSERT INTO orders (order_id, user_id) VALUES (1, 1);
UPDATE users SET last_order = NOW() WHERE user_id = 1;
COMMIT;

4. Avoid unnecessary operations:
   Check data beforehand to avoid unnecessary updates or inserts.

SELECT COUNT(*) FROM users WHERE user_id = 1;
-- Avoid unnecessary inserts/updates

Q6: How can I prevent INSERT or UPDATE errors?

Answer:

To prevent errors, use the following approaches.

• Verify data types:
  Ensure the data you insert/update matches the column’s data type.
• Set constraints properly:
  Correctly configure primary keys, unique keys, and foreign key constraints to maintain data integrity.
• Implement error handling:
  Add logic in your program to handle errors when they occur.

-- Roll back on error
START TRANSACTION;
INSERT INTO users (user_id, name, email) VALUES (1, 'Taro Yamada', 'taro@example.com');
ROLLBACK; -- As needed

8. Summary

In this article, we covered a wide range of topics: the basics and advanced usage of MySQL INSERT and UPDATE statements, practical use cases, error handling, and answers to common questions. Below is a recap of the key points.

Key Takeaways

1. INSERT Basics

• Use INSERT to insert new data into a table.
• You can insert multiple rows, enabling efficient operations.
• Be careful with NULL values and NOT NULL constraints.

2. UPDATE Basics

• Use UPDATE to modify existing data based on conditions.
• Always specify a WHERE clause to avoid unintentionally updating all rows.

3. Combining INSERT and UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE enables inserting new data and updating existing data in a single operation.
• REPLACE deletes data and inserts it again, so be mindful of triggers and foreign keys.

4. Practical Examples

• You learned how to use INSERT and UPDATE in use cases such as inventory management and user information management.
• We also presented best practices for efficiently processing multiple operations.

5. Errors and Fixes

• We explained causes and solutions for common problems such as duplicate entries, foreign key constraints, and NULL insertion errors.
• Using transactions and index design is important.

6. FAQ

• We answered common questions about choosing INSERT vs UPDATE, the scope of ON DUPLICATE KEY UPDATE, and performance optimization.

Next Steps

MySQL INSERT and UPDATE statements are fundamental to database operations and essential skills for application development. Based on what you learned in this article, consider the following next steps.

1. Learn transaction management:
   To perform more advanced database operations, deepen your understanding of how to use transactions.
2. Optimize index design:
   Learn index design to maintain query performance as data volume grows.
3. Improve logging for troubleshooting:
   Introduce log recording and analysis so you can quickly identify causes when errors occur.
4. Use the official MySQL documentation:
   For more details and the latest features, refer to the official MySQL documentation.

Final Note

We hope this article helps you understand INSERT and UPDATE and use them efficiently. Mastering basic data operations will improve your database management skills and enable you to handle more advanced application development.

Keep deepening your MySQL knowledge!