The World of MySQL & MariaDB

Complete MySQL UPDATE & SELECT Guide (Beginner-Intermediate)

SQL Basics
目次

1. Introduction

MySQL is a major database management system used in many web applications and systems. Among its functions, “data updates” are an unavoidable operation in daily operations. In particular, when updating existing data based on other tables or calculation results, you need to combine UPDATE statements with SELECT statements. This article provides a detailed explanation of advanced data manipulation that combines MySQL’s UPDATE and SELECT statements. It starts from the basics in an easy-to-understand way for beginners and covers a wide range of practical examples. It will serve as an ideal guide for those who want to learn efficient database update methods or improve their SQL skills.

2. Basic Syntax of UPDATE Statement

First, let’s review the basics of the UPDATE statement. The UPDATE statement is used to modify data in a specific row or multiple rows within a table.

Basic Syntax

The basic syntax of the UPDATE statement is as follows.
UPDATE table_name
SET column_name = new_value
WHERE condition;
  • Table name: The name of the table to be updated.
  • Column name: The name of the column to be updated.
  • New value: The value to set for the column.
  • Condition: The condition that limits which rows are updated.

Simple Example

Let’s take an example of updating a product’s price.
UPDATE products
SET price = 100
WHERE id = 1;
This query updates the price of the product with id 1 in the products table to 100.

Updating Multiple Columns

You can also update multiple columns at once.
UPDATE employees
SET salary = 5000, position = 'Manager'
WHERE id = 2;
In this example, the salary and position of the employee with id 2 in the employees table are updated simultaneously.

Importance of the WHERE Clause

If you omit the WHERE clause, all rows in the table will be updated. This can unintentionally modify data, so caution is required.
UPDATE products
SET price = 200;
This query sets the price of all products in the products table to 200.

3. Advanced UPDATE Using SELECT Statements

MySQL allows you to combine UPDATE statements with SELECT statements to update records based on data retrieved from other tables or specific conditions. This section explains two main approaches that leverage SELECT statements: subqueries and JOINs.

3.1 UPDATE Using Subqueries

Using subqueries, you can retrieve data that meets certain conditions with a SELECT statement and use it to perform updates. This method is relatively simple in structure and flexible to use.

Basic Syntax

UPDATE table_name
SET column_name = (SELECT column_name FROM other_table WHERE condition)
WHERE condition;

Example

For example, consider updating the price in the products table with the average price from the product_stats table.
UPDATE products
SET price = (SELECT average_price FROM product_stats WHERE product_stats.product_id = products.id)
WHERE EXISTS (SELECT * FROM product_stats WHERE product_stats.product_id = products.id);
  • Key point:
  • A subquery returns the value to be updated.
  • Using EXISTS ensures the update runs only when the subquery returns a result.

Caution

  • Subqueries must return a single value: If a subquery returns multiple rows, you get the error Subquery returns more than one row. To avoid this, use LIMIT or aggregate functions (e.g., MAX or AVG) to reduce the result to one row.

3.2 UPDATE Using JOIN

A method that often offers higher performance than subqueries is using JOIN with UPDATE. It is especially suitable when updating large amounts of data.

Basic Syntax

UPDATE tableA
JOIN tableB ON condition
SET tableA.column_name = tableB.column_name
WHERE condition;

Example

Next, we show an example that updates the discount rate in the orders table with the default_discount of the related customer.
UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.id
SET o.discount = c.default_discount
WHERE c.vip_status = 1;
  • Key point:
  • Using JOIN allows you to efficiently update while joining multiple tables.
  • In this example, the discount in the orders table is updated only for VIP customers in the customers table.

Caution

  • Performance: JOIN-based UPDATE is efficient for large data sets, but performance can degrade if appropriate indexes are not set on the join conditions.

Differences Between Subqueries and JOINs

ItemSubqueryJOIN
ConvenienceSimple and flexibleComplex but efficient
PerformanceSuitable for small datasetsIdeal for large datasets and multi-table updates
Implementation difficultyEasy for beginnersCondition setup is somewhat complex

4. Efficient UPDATE Techniques

Updating data in MySQL can be done with simple syntax, but when dealing with large datasets or performing frequent updates, an efficient approach that considers performance and safety is required. This section introduces practical techniques for optimizing UPDATE statements.

4.1 Update Only When Changes Exist

When updating data, updating only the rows that actually need changes reduces unnecessary writes and improves performance.

Basic Syntax

UPDATE table_name
SET column_name = new_value
WHERE column_name = new_value;

Example

This example updates a product’s price only when the existing price differs from the new price.
UPDATE products
SET price = 150
WHERE price != 150;
  • Benefits:
  • Avoid unnecessary writes.
  • Shorten database lock duration.

4.2 Using CASE Statements with Conditional Logic

When you need to set different values based on specific conditions, using a CASE statement is convenient.

Basic Syntax

UPDATE table_name
SET column_name = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE default_value
END;

Example

This example updates employee salaries based on performance evaluations.
UPDATE employees
SET salary = CASE
    WHEN performance = 'high' THEN salary * 1.1
    WHEN performance = 'low' THEN salary * 0.9
    ELSE salary
END;
  • Key Points:
  • Allows flexible updates based on conditions.
  • A method commonly used in practice.

4.3 Ensuring Safety with Transactions

When performing multiple updates, using a transaction to group the operations ensures safety and consistency.

Basic Syntax

START TRANSACTION;
UPDATE table_name1 SET ... WHERE condition;
UPDATE table_name2 SET ... WHERE condition;
COMMIT;

Example

This example manages a fund transfer between two accounts using a transaction.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
  • Key Points:
  • If an error occurs mid‑process, you can undo changes with ROLLBACK.
  • Ensures data integrity.

4.4 Optimizing with Indexes

By adding indexes to columns used in UPDATE conditions, you can improve lookup speed and overall performance.

Basic Example

CREATE INDEX idx_price ON products(price);
This speeds up UPDATE statements that use price as a condition.

4.5 Updating Large Datasets with Batch Processing

Updating a large dataset all at once can increase database load and degrade performance. In such cases, updating in small batches is effective.

Basic Syntax

UPDATE table_name
SET column_name = new_value
WHERE condition
LIMIT 1000;
  • Example:
  • Process 1,000 rows at a time and loop with a script.

5. Cautions and Best Practices

MySQL’s UPDATE statement is a convenient feature, but using it incorrectly can lead to performance degradation and data inconsistencies. This section covers the cautions when using UPDATE statements and best practices for production use.

5.1 Using Transactions

We recommend using transactions to safely execute multiple UPDATE statements. This ensures data integrity even if an error occurs mid-operation.

Cautions

  • Forgetting to start a transaction: If you don’t explicitly write START TRANSACTION, the transaction won’t be active.
  • Commit and rollback: Ensure you use COMMIT on successful completion and ROLLBACK when an error occurs.

Best Practice Example

START TRANSACTION;

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

COMMIT;
In this example, if an error occurs partway through, ROLLBACK can restore the data to its original state.

5.2 Proper Index Configuration

Adding indexes to columns used in UPDATE statement conditions improves lookup speed and overall performance.

Cautions

  • Excessive indexes: Overusing indexes increases overhead during data updates. Aim to create only the essential indexes.

Best Practice Example

When updating product prices, indexing the price and id columns is effective.
CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_id ON products(id);
This speeds up UPDATE queries that use price or id in the WHERE clause.

5.3 Managing Locks

When executing UPDATE in MySQL, the affected rows are locked. Updating large amounts of data at once can impact other queries.

Cautions

  • Long-running locks: If a lock persists for a long time, other transactions wait, degrading overall system performance.

Best Practice Example

  • Limit the number of rows updated (use batch processing).
  • Narrow the range with a WHERE clause.
UPDATE orders
SET status = 'completed'
WHERE status = 'pending'
LIMIT 1000;

5.4 Cautions When Using Subqueries

When using a SELECT statement in an UPDATE, a subquery that returns multiple rows causes an error. Also, subqueries handling large data sets can degrade performance.

Cautions

  • Restrict results to a single row: Use aggregate functions (e.g., MAX, AVG) or LIMIT to ensure the result is a single row.

Best Practice Example

UPDATE products
SET price = (
  SELECT AVG(price)
  FROM product_stats
  WHERE product_stats.category_id = products.category_id
)
WHERE EXISTS (
  SELECT * FROM product_stats WHERE product_stats.category_id = products.category_id
);

5.5 Reviewing the Execution Plan

Before running complex UPDATE queries, use EXPLAIN to review the execution plan and identify performance issues in advance.

Best Practice Example

EXPLAIN UPDATE products
SET price = 200
WHERE category_id = 1;
This lets you verify whether indexes are used appropriately and that a full table scan is not occurring.

5.6 Ensuring Backups

If an UPDATE statement is run by mistake, a large amount of data can be lost. Therefore, we recommend taking a database backup before performing critical operations.

Best Practice Example

Create backups using MySQL’s dump tool.
mysqldump -u username -p database_name > backup.sql

6. FAQ (Frequently Asked Questions)

Here we have compiled the most common questions and answers related to MySQL’s UPDATE statements. This information helps resolve practical doubts and supports efficient data updates.

Q1: Can a UPDATE statement update multiple tables at once?

A1: In MySQL, a single UPDATE statement cannot update multiple tables simultaneously. However, you can join multiple tables (JOIN) to update data in one table.

Example: Updating tables using JOIN

UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.id
SET o.discount = c.default_discount
WHERE c.vip_status = 1;

Q2: How can I improve the performance of UPDATE statements?

A2: You can improve performance using the following methods.
  • Set indexes appropriately: Create indexes on columns used in the WHERE clause.
  • Avoid unnecessary updates: Specify conditions that target only rows that need to be updated.
  • Use batch processing: Update large amounts of data in small chunks to reduce lock impact.

Example of batch processing

UPDATE products
SET stock = stock - 1
WHERE stock > 0
LIMIT 1000;

Q3: What should I watch out for when using subqueries in UPDATE statements?

A3: When using subqueries in an UPDATE statement, keep the following points in mind.
  • Subquery must return a single row: An error occurs if the subquery returns multiple rows.
  • Performance: Overusing subqueries can degrade performance, especially with large datasets.

Example of a subquery

UPDATE employees
SET salary = (SELECT AVG(salary) FROM department_salaries WHERE employees.department_id = department_salaries.department_id)
WHERE EXISTS (SELECT * FROM department_salaries WHERE employees.department_id = department_salaries.department_id);

Q4: What happens if I perform an UPDATE without using a transaction?

A4: If you don’t use a transaction, an error occurring partway through an UPDATE will cause earlier operations to be committed, potentially compromising data integrity. For processes that include multiple UPDATE statements, using a transaction to maintain data consistency is recommended.

Example using a transaction

START TRANSACTION;

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

COMMIT;

Q5: What should I do if I run a UPDATE statement without specifying a condition?

A5: Running a UPDATE without a condition updates every row in the table. To prevent this, it’s important to back up the database before making changes. If only a few rows are affected, you can manually correct them or restore the data from the backup.

Q6: I encountered a Deadlock when using an UPDATE statement in MySQL. What should I do?

A6: Deadlock occurs when multiple transactions each need a lock that the other holds, causing them to wait. You can address it using the following methods.
  • Standardize the update order: Update rows in the same order across all transactions.
  • Split transactions: Reduce the number of rows updated at once, making each transaction smaller.

7. Summary

In this article, we explained in detail how to use MySQL’s UPDATE statement effectively, from basics to advanced topics. Below, we review the key points of each section.

1. Introduction

  • The MySQL UPDATE statement is an essential tool for modifying databases.
  • By combining it with SELECT statements, you can efficiently update data based on other tables or calculated results.

2. Basic Syntax of UPDATE Statements

  • We covered the basic form of the UPDATE statement and simple usage examples.
  • Remembering to specify conditions (the WHERE clause) prevents unintended updates to all rows.

3. Advanced UPDATE Using SELECT Statements

  • Flexible update methods using subqueries.
  • How to efficiently update data across multiple tables using JOINs.
  • We also examined the differences between subqueries and JOINs and when to use each.

4. Efficient UPDATE Techniques

  • Techniques to update only when changes exist, avoiding unnecessary updates.
  • Examples of conditional logic using the CASE statement.
  • How to improve performance by leveraging transactions, setting indexes, and using batch processing.

5. Cautions and Best Practices

  • The importance of using transactions to maintain data integrity.
  • Proper management of indexes and locks.
  • How to handle potential errors when using subqueries and how to examine execution plans.

6. FAQ

  • We presented concrete UPDATE usage examples and solutions to common real‑world questions.
  • Topics include updating multiple tables, the importance of transactions, and handling deadlocks.

Next Steps

Based on what you learned in this article, try the following steps.
  1. Run a basic UPDATE statement to verify the syntax.
  2. Try combining it with SELECT statements or JOINs to match real‑world scenarios.
  3. When updating large datasets, use transactions and indexes to assess performance.
If you want to further improve your SQL skills, we recommend studying the following topics.
  • MySQL index optimization
  • Advanced transaction management
  • SQL performance tuning
The MySQL UPDATE statement is a crucial skill for database operations. Use this article as a reference to apply it effectively in your work. Hands‑on practice with queries will help you sharpen your skills!