The World of MySQL & MariaDB

Bulk Update MySQL Records and Columns – Beginner to Pro

SQL Basics
目次

1. Introduction

MySQL is used in many web applications and database management, and updating data is extremely important for daily operations and application maintenance. In particular, for systems handling large volumes of data or when multiple records need to be updated at once, leveraging MySQL’s UPDATE statement to operate efficiently is required. This article explains in detail how to update multiple records and columns in bulk using MySQL’s UPDATE statement. From simple usage to more complex conditional updates, we introduce them in order, making it a useful resource for those who want to perform complex update operations with MySQL.

2. Basic Syntax of UPDATE Statements

The MySQL UPDATE statement is a syntax for updating data in a table based on specific conditions. First, let’s look at the basic syntax of the UPDATE statement and how to update a single record or column.

Basic Syntax

The basic syntax of MySQL’s UPDATE statement is as follows.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name: Specify the name of the table to be updated.
  • SET clause: Specify the columns to update and their new values. When updating multiple columns at once, separate column‑value pairs with commas.
  • WHERE clause: Specify the condition for the records to be updated. If you omit the condition, all records in the table will be updated, so be careful.

Example of Updating a Single Record or Column

As a basic usage example, let’s see how to update a single record or column.
UPDATE users
SET name = 'Tanaka'
WHERE id = 1;
This SQL statement updates the name column of the record with id 1 in the users table to “Tanaka”. By specifying a WHERE clause, you can update only the specific record.

3. Bulk Updating Multiple Records

When updating multiple records in bulk, you can specify multiple conditions in the WHERE clause. For example, you can use IN or OR clauses to specify several conditions and efficiently update multiple records.

Updating Multiple Records Using IN Clause

Using the IN clause allows you to update records that match a specific list of values.
UPDATE users
SET status = 'active'
WHERE id IN (1, 3, 5, 7);
In this SQL statement, within the users table, records where id is 1, 3, 5, or 7 have their status column updated to ‘active’. Using the IN clause lets you bulk update multiple records that meet the condition.

Specifying Multiple Conditions Using OR Clause

The OR clause allows you to combine and specify multiple conditions.
UPDATE users
SET status = 'inactive'
WHERE id = 2 OR id = 4 OR id = 6;
In this SQL statement, the status column of records where id is 2, 4, or 6 is updated to ‘inactive’. Using the OR clause enables you to update records that match multiple conditions simultaneously.

4. Updating Multiple Columns Simultaneously

In MySQL’s UPDATE statements, you can update multiple columns at the same time. This is useful when you need to change several pieces of information at once while maintaining data consistency.

Example of Updating Multiple Columns

When updating multiple columns simultaneously, specify the column names and values in the SET clause, separated by commas.
UPDATE products
SET price = price * 1.1, stock = stock - 1
WHERE id = 10;
In this SQL statement, for the record in the products table where id is 10, the price column is increased by 10% and the stock column is decreased by 1. By specifying multiple columns in the SET clause, you can efficiently update several pieces of information.

5. Conditional Update Using CASE Statements

In MySQL’s UPDATE statements, you can use CASE expressions to set different values based on conditions. This allows you to flexibly modify update contents according to multiple conditions, making complex update operations simple to execute.

Basic Syntax Using CASE Expressions

The basic syntax for an UPDATE statement using a CASE expression is as follows.
UPDATE table_name
SET column_name = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE default_value
END
WHERE condition;
  • Column name: Specifies the column you want to update.
  • Condition: Specify the condition in the WHEN clause, and set the value to apply in the THEN clause when it matches.
  • Default value: The value set when none of the conditions match (optional).

Practical Example Using CASE

Here, let’s look at an example that updates salaries in the employees table based on job position.
UPDATE employees
SET salary = CASE
    WHEN position = 'Manager' THEN salary * 1.1
    WHEN position = 'Developer' THEN salary * 1.05
    WHEN position = 'Intern' THEN salary * 1.02
    ELSE salary
END;
In this SQL statement, for each record in the employees table, the salary column is updated according to the value of the position column.

Conditional Update of Multiple Columns

CASE expressions can also be applied to multiple columns. In the example below, the salary and bonus columns in the employees table are updated with different values based on job position and years of service.
UPDATE employees
SET 
    salary = CASE
        WHEN position = 'Manager' AND years_of_service >= 5 THEN salary * 1.15
        WHEN position = 'Developer' AND years_of_service >= 3 THEN salary * 1.1
        ELSE salary
    END,
    bonus = CASE
        WHEN position = 'Manager' THEN bonus + 1000
        WHEN position = 'Developer' THEN bonus + 500
        ELSE bonus
    END;
In this SQL statement, salaries and bonuses are conditionally updated at once based on job position and years of service. Using CASE expressions enables flexible updates based on multiple conditions.

6. Updating Multiple Tables Using JOIN

In MySQL, you can use a JOIN clause to combine multiple tables and update records based on specific conditions. This allows you to reference data from different tables while updating one table, enabling complex data manipulation.

Basic Syntax for UPDATE Statements Using JOIN

When updating multiple tables with JOIN, the basic syntax is as follows.
UPDATE tableA
JOIN tableB ON tableA.column = tableB.column
SET tableA.updated_column = new_value
WHERE condition;
  • Table A and Table B: Specify the target table for the update (Table A) and the reference table (Table B).
  • ON clause: Specify the JOIN condition and define which column to join the tables on.
  • SET clause: Specify the column to update and its new value.
  • WHERE clause: Specify the update condition, updating only the matching records.

Practical Example Using JOIN

For example, let’s look at an example that joins the orders table with the customers table to update the status of orders related to a specific customer.
UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.status = 'Shipped'
WHERE customers.vip_status = 'Yes';
In this SQL statement, the status column of the orders table records related to customers whose vip_status in the customers table is “Yes” is updated to “Shipped”. By using a JOIN clause, you can update based on information from other tables.

JOIN Update with Multiple Conditions

You can combine multiple conditions to specify more granular criteria. In the example below, the status of orders related to a specific customer is changed in bulk based on conditions.
UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.status = CASE
    WHEN customers.vip_status = 'Yes' THEN 'Priority'
    WHEN customers.vip_status = 'No' AND orders.amount > 10000 THEN 'Review'
    ELSE orders.status
END
WHERE orders.date >= '2024-01-01';
By leveraging JOIN, you can perform flexible data updates that adapt to conditions.

7. Performance and Considerations

When using MySQL UPDATE statements to modify multiple rows or columns in bulk, especially with large data sets, performance must be considered. Below are the key points and cautions you should know to improve update performance and maintain data integrity.

Performance Optimization Tips

Leveraging Indexes

When updating records based on specific conditions using a WHERE clause, adding an index to the relevant columns improves search speed. Indexes enhance query performance, allowing efficient processing even when the update targets a large volume of data.
CREATE INDEX idx_customer_id ON orders(customer_id);
However, having too many indexes can actually degrade performance, so it’s recommended to index only the essential columns.

Reducing Load with Batch Processing

Updating a large number of rows at once can place a heavy load on the database server and slow response times. For massive updates, using batch processing (executing the operation in multiple passes) can reduce the load on the server.
UPDATE orders
SET status = 'Processed'
WHERE status = 'Pending'
LIMIT 1000;
By combining this with a script, you can repeatedly run batch updates for efficient data modification.

Using Transactions

When multiple UPDATE statements are related or data integrity is critical, using a transaction ensures consistency. With a transaction, any error that occurs during the update can roll back all changes.
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Lock Management

Executing an UPDATE can cause table locks. This is especially important when multiple users access the same table concurrently. For example, using row-level locks allows other users to work on different rows simultaneously, enabling parallel processing. Avoiding full-table locks improves database responsiveness.

8. Summary

In this article, we provided a detailed explanation of efficient methods for updating multiple records and columns using MySQL’s UPDATE statement, covering everything from basic usage to advanced techniques. When updating multiple pieces of data in MySQL, you need to consider data volume, processing speed, and data integrity.

Review of Main Points

  1. Basics of UPDATE Statements
  • Understanding the basic syntax of the UPDATE statement allows you to safely update individual columns or records.
  1. Bulk Updating Multiple Records
  • We showed how to efficiently update multiple records that meet specific conditions using WHERE, IN, and OR clauses.
  1. Simultaneous Updating of Multiple Columns
  • By using the SET clause, you can update several columns within the same record at once, enabling efficient updates while maintaining data consistency.
  1. Conditional Updates Using CASE Statements
  • Leveraging CASE statements lets you perform different updates in a single operation based on conditions, simplifying complex update logic.
  1. Updating Multiple Tables Using JOIN
  • By referencing data from other tables while updating specific records, you can improve overall database integrity.
  1. Performance and Considerations
  • We learned how to perform efficient and safe data updates by leveraging indexes, batch processing, and transactions. Also, be mindful of lock management to optimize database performance.

Conclusion

Efficiently updating data in MySQL is a particularly important skill in database administration. Mastering the UPDATE statement can boost operational efficiency and optimize overall system performance. Be sure to apply the techniques presented in this article to your real-world tasks and projects.