The World of MySQL & MariaDB

MySQL INSERT & UPDATE: Master Efficient Data Management

SQL Basics
目次

1. Introduction

MySQL is one of the most widely used tools among database management systems. Among its features, INSERT and UPDATE are fundamental operations that are frequently used to add and modify data within a database. They play a crucial role in everyday tasks such as managing product information on e‑commerce sites and registering or updating user data. Understanding these basic operations correctly and using them efficiently is extremely important for database administration. This article is aimed at beginners through intermediate users and provides a detailed explanation of the basic usage of INSERT and UPDATE, as well as practical examples. It also covers tips and best practices to help you manage data more effectively. In the next section, we’ll explain the basic syntax of the INSERT statement and how to use it. Whether you want to review SQL fundamentals or learn something new, this content is designed to be useful.

2. Basic Syntax and Usage of INSERT Statements

The INSERT statement is used when adding new data to a database. This SQL statement is a very fundamental syntax in MySQL, yet it plays a crucial role in supporting the foundation of data management. In this section, we will explain the basic syntax of INSERT statements, concrete examples, and points to consider when using them.

Basic Syntax of INSERT Statements

The basic syntax of the INSERT statement is as follows.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • Table name: The name of the table into which data will be inserted.
  • Column1, Column2, …: The column names that correspond to the data being inserted.
  • Value1, Value2, …: The actual data to be inserted.

Example

For example, to add a new user’s name and email address to the users table, you would use the following SQL statement.
INSERT INTO users (name, email)
VALUES ('Taro Yamada', 'taro@example.com');
This statement adds ‘Taro Yamada’ to the name column and ‘taro@example.com’ to the email column of the users table.

INSERT Statement Options

1. Inserting Multiple Rows at Once

The INSERT statement can also insert multiple rows at once. In the example below, two users are added to the users table simultaneously.
INSERT INTO users (name, email)
VALUES 
  ('Hanako Yamada', 'hanako@example.com'),
  ('Jiro Sato', 'jiro@example.com');
This method is efficient when adding multiple rows.

2. Inserting Data into Specific Columns Only

It is not necessary to insert data into every column. You can add data to only specific columns.
INSERT INTO users (name)
VALUES ('Ichiro Suzuki');
In this case, the email column will be set to NULL or its default value.

Points to Consider When Using

  1. Pay attention to column-value correspondence
  • The number and order of the specified columns must match the number and order of the values provided in VALUES.
  • Example: sql INSERT INTO users (name, email) VALUES ('Yoko Ota', 'yoko@example.com'); -- correct
  1. Check unique key constraints
  • You need to verify in advance that the data being inserted does not duplicate existing data.
  • Inserting duplicate data will result in an error.
  1. Utilize default values
  • If you do not specify a value for a particular column, the default value set during table design will be inserted.

3. Basic Syntax and Usage of UPDATE Statements

The UPDATE statement is an SQL command used to modify or refresh existing data. For example, it is used to change user information or update inventory counts. This section explains the basic syntax of UPDATE statements, practical examples, and points to consider when using them.

Basic Syntax of UPDATE Statements

The basic syntax of an UPDATE statement is as follows.
UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;
  • Table name: The table to be updated.
  • SET clause: The columns to update and their new values.
  • WHERE clause: The condition that specifies which rows to update. If omitted, all rows in the table will be updated.

Example

The following is an example of changing the email address of a specific user in the users table.
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;
This statement updates the email address of the user whose id is 1 to ‘newemail@example.com’.

Precautions When Using

1. Importance of the WHERE Clause

If you omit the WHERE clause, all rows in the table will be updated. In the example below, the email column would be changed to the same value for every row, so caution is required.
UPDATE users
SET email = 'sameemail@example.com'; -- all rows will be updated
Be sure to specify the necessary conditions to avoid unintentionally modifying all data.

2. Updating Multiple Columns

When updating multiple columns at once, list the columns in the SET clause separated by commas.
UPDATE users
SET name = 'Jiro Yamada', email = 'jiro@example.com'
WHERE id = 2;
This statement updates both the name and email address of the user whose id is 2.

3. Table Locks and Performance

  • Using an UPDATE statement locks the target table or rows, which can affect performance when updating large amounts of data.
  • Consider using batch processing or leveraging indexes as needed.

Practical Examples

Updating a User’s Active Status

The example below changes the status to ‘active’ for users who meet a specific condition.
UPDATE users
SET status = 'active'
WHERE last_login > '2025-01-01';
This statement updates users whose last login date is on or after January 1, 2025 to an active status.

Decreasing Inventory Count

When reducing a product’s inventory, you can calculate based on the current value as shown below.
UPDATE products
SET stock = stock - 1
WHERE product_id = 101;
This statement decreases the inventory of the product with product_id 101 by 1.

Advanced Use of UPDATE Statements

An UPDATE statement can also retrieve values from another table to perform the update. For example, the syntax for updating based on values from another table is as follows.
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.order_date
WHERE o.order_status = 'completed';
This statement reflects the order completion date from the orders table into the last_order_date column of the users table.

Summary

The UPDATE statement is an essential operation for modifying data. By accurately understanding the basic syntax and using the WHERE clause appropriately, you can manipulate data safely and efficiently. The next section will provide a detailed explanation of how to combine INSERT and UPDATE operations.

4. How to Combine INSERT and UPDATE Operations

In MySQL, you can combine INSERT and UPDATE to manage data efficiently. This allows you to insert when the data does not exist and update when it does, all in a single SQL statement. This section focuses on using ON DUPLICATE KEY UPDATE.

ON DUPLICATE KEY UPDATE What Is It?

ON DUPLICATE KEY UPDATE is a handy feature that merges the behavior of INSERT and UPDATE statements. Using this syntax streamlines inserting and updating data. The basic syntax is as follows.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = new_value1, column2 = new_value2, ...;
  • INSERT part: Operation that adds new data.
  • ON DUPLICATE KEY UPDATE part: Operation that updates data when a unique key conflict is detected during insertion.

Examples

Basic Example

The following inserts data into the users table and updates an existing username.
INSERT INTO users (id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = 'Taro Yamada', email = 'taro@example.com';
This statement performs the following actions:
  1. If no record with id = 1 exists, a new row is inserted.
  2. If a record with id = 1 exists, the name and email are updated.

Inventory Management Example

A useful syntax for updating or inserting product stock levelspre>INSERT INTO inventory (product_id, stock) VALUES (101, 50) ON DUPLICATE KEY UPDATE stock = stock + 50; This statement does the following:
  1. If a product with product_id = 101 does not exist, a new row is inserted.
  2. If it already exists, 50 is added to the stock (stock).

Cautions

1. Need for a Unique Key

ON DUPLICATE KEY UPDATE works only when the table has a unique key (PRIMARY KEY or UNIQUE index). It will error if no unique key is defined.

2. AUTO_INCREMENT Increment

When using ON DUPLICATE KEY UPDATE, the AUTO_INCREMENT value still increments even if a duplicate key is detected. This is because MySQL advances the counter when it attempts the insert.
INSERT INTO users (id, name)
VALUES (NULL, 'Hanako Suzuki')
ON DUPLICATE KEY UPDATE
name = 'Hanako Suzuki';
In this case, the AUTO_INCREMENT value increases even though a duplicate occurs.

3. Impact on Performance

When dealing with large volumes of data, ON DUPLICATE KEY UPDATE can affect performance. Especially if frequent insert/update operations are required, consider using transactions to improve efficiency.

Advanced Examples

Data Consolidation Across Tables

You can also use ON DUPLICATE KEY UPDATE to insert or update while pulling data from another table.
INSERT INTO users (id, name, email)
SELECT id, full_name, dept
FROM external_users
ON DUPLICATE KEY UPDATE
name = VALUES(name),
department = VALUES(department);
This statement inserts data from the external_users table into the users table and updates any duplicates.

Summary

ON DUPLICATE KEY UPDATE is a powerful tool for streamlining data management in MySQL. By leveraging this feature, you can perform inserts and updates in a single SQL statement, leading to cleaner code and improved performance. The next section will present more detailed use cases based on real‑world scenarios.

5. Practical Examples: Learning Through Specific Scenarios

Here we introduce how to use INSERT ... ON DUPLICATE KEY UPDATE and basic INSERT/UPDATE statements in real-world scenarios. By referring to these examples, you can acquire practical skills that can be applied on the job.

Scenario 1: E‑commerce Site Product Inventory Management

On an e‑commerce site, products may be newly added or existing products may be restocked. In such cases, inventory levels need to be managed efficiently.

Practical Example

The following SQL statement adds a new product or updates the inventory count of an existing product.
INSERT INTO inventory (product_id, product_name, stock)
VALUES (101, 'laptop', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;
  • Explanation:
  1. If a product with ID 101 does not exist, a new record is inserted.
  2. If a product with ID 101 already exists, 50 is added to the inventory count (stock).
In this way, you can perform efficient inventory updates in product management.

Scenario 2: User Registration and Update

In subscription services, new users may be registered and existing user information may be updated. Both operations can be handled with a single SQL statement.

Practical Example

The following SQL statement adds a new user or updates the name and email address of an existing user.
INSERT INTO users (id, name, email)
VALUES (1, 'Ichiro Tanaka', 'tanaka@example.com')
ON DUPLICATE KEY UPDATE
name = 'Ichiro Tanaka',
email = 'tanaka@example.com';
  • Explanation:
  1. If there is no data with user ID 1, a new record is inserted.
  2. If data with user ID 1 exists, the name and email address are updated.
This method helps with bulk management of user information.

Scenario 3: Updating Login History

In web applications, it is common to record users’ login history. Let’s look at an example that registers a new user while updating the last login timestamp for existing users.

Practical Example

INSERT INTO login_history (user_id, last_login)
VALUES (1, NOW())
ON DUPLICATE KEY UPDATE
last_login = NOW();
  • Explanation:
  1. If a record with user_id 1 does not exist, a new record is inserted.
  2. For an existing user, the last login timestamp (last_login) is updated to the current time (NOW()).
This SQL statement provides a way to manage login information efficiently.

Scenario 4: Periodic Product Price Updates

When prices need to be updated regularly, using ON DUPLICATE KEY UPDATE allows you to add new products and update existing product prices simultaneously.

Practical Example

INSERT INTO products (product_id, product_name, price)
VALUES (201, 'smartphone', 69900)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
  • Explanation:
  1. If a product with product_id 201 does not exist, a new product record is inserted.
  2. If the product already exists, its price (price) is updated.
This approach provides a concise way to change product prices.

Scenario 5: Integrated Data Management

When importing data from external systems, you need to manage it so that it does not duplicate existing data.

Practical Example

INSERT INTO employees (employee_id, name, department)
SELECT id, full_name, dept
FROM external_employees
ON DUPLICATE KEY UPDATE
name = VALUES(name),
department = VALUES(department);
  • Explanation:
  1. Data is retrieved from the external_employees table and inserted into the employees table.
  2. If a duplicate is detected, the name and department information are updated.
Such data integration plays an important role in data exchange between systems.

Summary

These scenarios demonstrate how to use INSERT ... ON DUPLICATE KEY UPDATE to manage data efficiently. To handle situations that frequently arise in real-world work, refer to these examples and use the appropriate SQL statements.

6. Cautions and Best Practices

MySQLでINSERTやUPDATE、さらにはON DUPLICATE KEY UPDATEを使用する際には、いくつかの注意点を理解し、ベストプラクティスを活用することで、パフォーマンスを向上させ、安全で効率的なデータ操作を実現できます。このセクションでは、実務で役立つ注意点と推奨される方法を紹介します。

Cautions

1. Specify the WHERE clause accurately

  • Problem: UPDATE文でWHERE句を指定しない場合、テーブル内のすべての行が更新される可能性があります。
  • Solution: 必ず適切な条件をWHERE句に設定し、対象行を限定する。
  UPDATE users
  SET email = 'updated@example.com'
  WHERE id = 1; -- correct

2. Setting Unique Keys

  • Problem: ON DUPLICATE KEY UPDATEを利用する際に、ユニークキー（PRIMARY KEYまたはUNIQUEインデックス）が存在しない場合、エラーが発生します。
  • Solution: データが重複し得るカラムにユニークキーを設定する。
  ALTER TABLE users ADD UNIQUE (email);

3. Handling AUTO_INCREMENT

  • Problem: ON DUPLICATE KEY UPDATEを使用すると、重複時でもAUTO_INCREMENT値が増加します。
  • Solution: AUTO_INCREMENTを利用する場合、影響を受けないよう適切なユニークキー設計を行うか、値を明示的に管理する。

4. Impact on Performance

  • Problem: 大量データをINSERTやUPDATEで処理すると、データベースの負荷が高くなり、速度が低下します。
  • Solution:
  • トランザクションを使用して処理を一括管理。
  • 必要に応じてバッチ処理を実行。

Best Practices

1. Using Transactions

データの整合性を保つために、複数の操作をトランザクション内で実行することを推奨します。
START TRANSACTION;

INSERT INTO users (id, name) VALUES (1, '山田太郎')
ON DUPLICATE KEY UPDATE name = '山田太郎';

UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;

COMMIT;
  • トランザクションを利用することで、途中でエラーが発生した場合にロールバックが可能になり、データの一貫性を確保できます。

2. Leveraging Indexes

INSERTやUPDATEのパフォーマンスを向上させるため、適切にインデックスを設定します。
CREATE INDEX idx_user_email ON users (email);
  • インデックスはデータ検索を高速化しますが、過剰な設定はINSERTやUPDATEの速度を低下させる可能性があるため注意が必要です。

3. Error Handling

エラー時の処理を明確に定義しておくことで、予期しない挙動を防ぐことができます。
DELIMITER //
CREATE PROCEDURE insert_user(IN user_id INT, IN user_name VARCHAR(255))
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'An error occurred';
    END;
    START TRANSACTION;
    INSERT INTO users (id, name) VALUES (user_id, user_name)
    ON DUPLICATE KEY UPDATE name = user_name;
    COMMIT;
END;
//
DELIMITER ;

4. Setting Default Values

テーブル設計時にデフォルト値を設定しておくことで、INSERT時の省略可能なカラムを明確にし、エラーを減らします。
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active'
);

5. Processing Large Data Sets

大量データの処理では、一度に挿入・更新する行数を制限し、分割して実行することでパフォーマンスを最適化します。
INSERT INTO large_table (col1, col2)
VALUES
  (1, 'value1'),
  (2, 'value2'),
  ...
  (1000, 'value1000'); -- choose an appropriate number of rows

Common Pitfalls and Countermeasures

  • Pitfall 1: Mistakes handling duplicate data → 解決: ユニークキーとON DUPLICATE KEY UPDATEを活用。
  • Pitfall 2: Performance degradation due to table locks → 解決: インデックスとトランザクションを活用し、処理範囲を限定。
  • Pitfall 3: Wasting resources by updating unnecessary columns → 解決: 更新対象カラムを必要最小限に絞る。

Summary

MySQLのINSERTやUPDATEを安全かつ効率的に使用するためには、注意点を理解し、ベストプラクティスを取り入れることが重要です。トランザクションやインデックス、エラーハンドリングを適切に利用することで、データベース操作の信頼性とパフォーマンスを向上させることができます。

7. FAQ

Here we answer frequently asked questions about MySQL INSERT and UPDATE, ON DUPLICATE KEY UPDATE. This supplements the material covered in previous sections and aims to resolve practical doubts.

Q1: Why does the AUTO_INCREMENT value increase when using ON DUPLICATE KEY UPDATE?

Answer: In MySQL, an INSERT operation assigns an AUTO_INCREMENT value. Even if the data is duplicated and the operation proceeds to an update (ON DUPLICATE KEY UPDATE), the AUTO_INCREMENT counter is incremented at that point. This is how MySQL works, and there is no way to prevent the AUTO_INCREMENT value from being skipped. Mitigation:
  • To manage predictable ids, consider avoiding AUTO_INCREMENT and managing IDs manually.

Q2: What is the difference between ON DUPLICATE KEY UPDATE and REPLACE INTO?

Answer:
  • ON DUPLICATE KEY UPDATE: If a duplicate key is detected, the corresponding record is updated. Existing data is retained and not deleted.
  • REPLACE INTO: If a duplicate key is detected, the existing record is deleted and a new record is inserted. As a result, related foreign keys or historical data may be lost.
Guidelines for Use:
  • Choose ON DUPLICATE KEY UPDATE when you want to preserve data history.
  • Use REPLACE INTO when you need to completely replace old data.

Q3: How can you insert multiple rows at once with an INSERT statement?

Answer: With an INSERT statement, you can insert multiple rows at once using the VALUES clause.
INSERT INTO users (name, email)
VALUES 
  ('Taro Yamada', 'taro@example.com'),
  ('Hanako Sato', 'hanako@example.com'),
  ('Jiro Suzuki', 'jiro@example.com');
This method improves performance compared to inserting rows individually.

Q4: How do you specify multiple conditions in a WHERE clause?

Answer: To specify multiple conditions, use the AND or OR operators.
UPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01' AND status = 'active';
  • AND: Applies when both conditions are met.
  • OR: Applies when either condition is met.

Q5: In what situations should you use transactions?

Answer: You should use transactions when data integrity is critical, such as in the following cases.
  1. When multiple database operations need to be executed atomically:
   START TRANSACTION;
   INSERT INTO orders (order_id, user_id) VALUES (1, 123);
   UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;
   COMMIT;
  1. When you want to revert changes if an error occurs: Using a transaction allows you to roll back (restore to the original state) when an error occurs.

Q6: How can you efficiently process large-scale INSERTs and UPDATEs?

Answer: For large data operations, consider the following approaches.
  • Bulk operations: Insert multiple rows at once with an INSERT statement.
  INSERT INTO users (name, email)
  VALUES 
    ('Ichiro Tanaka', 'ichiro@example.com'),
    ('Hanako Sato', 'hanako@example.com');
  • Use of indexes: Set appropriate indexes to improve the speed of data retrieval and updates.
  • Divide the workload: Instead of processing all data at once, execute it in smaller batches.
  UPDATE inventory
  SET stock = stock - 1
  WHERE product_id BETWEEN 100 AND 200;

Q7: What are the benefits of using default values?

Answer: By defining default values, columns omitted during INSERT are automatically populated. This simplifies code and helps prevent errors.
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO users (name)
VALUES ('Taro Yamada'); -- status is automatically set to 'active'

Summary

The FAQ section provided detailed explanations of common practical questions. By applying this knowledge, you can use MySQL INSERT and UPDATE operations more efficiently and accurately.

8. Summary

In this article, we systematically explained everything from the basic operations of INSERT and UPDATE statements in MySQL to advanced techniques for efficiently managing data. Aimed at beginners to intermediate users, we included practical knowledge and best practices for real‑world work, focusing on the following points.

Review of Key Points

  1. Basic syntax of INSERT and UPDATE statements
  • You learned how INSERT adds new data and how UPDATE modifies existing data.
  • We covered important considerations such as WHERE clauses and multi‑row operations.
  1. Using ON DUPLICATE KEY UPDATE
  • We introduced ON DUPLICATE KEY UPDATE as an efficient way to insert new rows or update existing ones in a single SQL statement.
  • Through concrete examples like inventory management and login‑history updates, we showed how to apply it in practice.
  1. Practical usage examples
  • We presented SQL statements based on realistic scenarios such as e‑commerce inventory control and user‑information updates.
  • This should make it clear how to leverage these techniques in actual work.
  1. Precautions and best practices
  • We explained efficient and safe operation methods, including proper use of WHERE clauses, transaction‑based data integrity, and index utilization.
  1. Resolving questions with FAQs
  • By answering common questions, we supported deeper understanding and helped solve real‑world issues.

Value You’ll Gain from This Article

By accurately understanding and applying MySQL’s INSERT, UPDATE, and ON DUPLICATE KEY UPDATE, you should be able to reap benefits such as:
  • Streamlined database management: Consolidate insert and update operations with concise SQL.
  • Error prevention: Safe operations using WHERE clauses and transactions.
  • Improved real‑world applicability: Leverage concrete examples that match realistic scenarios.

Next Steps

Use this article as a reference and try applying INSERT and UPDATE in your own projects. If you want to keep advancing your skills, we recommend learning the following topics:
  • MySQL performance tuning
  • Building complex queries (subqueries, JOINs)
  • Using triggers and stored procedures in MySQL
We hope that through this article your MySQL data‑manipulation skills will improve and lead to higher productivity at work. Continue learning more advanced database techniques and apply them to your projects!