SoFunction
Updated on 2025-03-04

Detailed explanation of UPDATE in MySQL database

Preface

MySQLUPDATEThe statement is used to modify records that already exist in the database table. This article will introduce it in detail.UPDATEBasic syntax, advanced usage, performance optimization strategies, and considerations for statements help you better understand and apply this important SQL command.

1. Basic syntax

Single table update

The basic syntax for single table update is as follows:

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count]
  • LOW_PRIORITY: If specifiedLOW_PRIORITYOptions, thenUPDATEThe operation is delayed until no other client is reading data from the table.
  • IGNORE: If specifiedIGNOREOptions, then when an error is encountered (such as primary key or unique index conflict),UPDATEThe operation will not be interrupted, but will issue a warning.
  • table_name: The name of the table to be updated.
  • SET column1 = value1, column2 = value2, …: Specifies the column to be updated and its new value. Multiple columns can be updated at the same time, using commas,Separate.
  • WHERE condition: Optional, used to specify which rows should be updated. If notWHEREclause, then all rows in the table will be updated.
  • ORDER BY …: Optional, used to specify the order of update rows.
  • LIMIT row_count: Optional, used to limit the maximum number of rows updated.

Example

-- Update table students middle id for 1 Records of，Will name 字段设for 'Zhang San'
UPDATE students
SET name = 'Zhang San'
WHERE id = 1;

-- Update table students middle所有记录，Will age Field addition 1
UPDATE students
SET age = age + 1;

2. Advanced usage

Update with expression

-- Set the table students All records in age Field addition 1
UPDATE students
SET age = age + 1;

Update with subquery

-- Set the table students middle name for 'Zhang San' Record of class_id 更新for表 classes middle name for 'Mathematics Class' of class_id
UPDATE students
SET class_id = (SELECT id FROM classes WHERE name = 'Mathematics Class')
WHERE name = 'Zhang San';

Update multiple tables

-- Update table orders and order_details，Total order amount is greater than 1000 The order status is set to 'Completed'
UPDATE orders o
JOIN order_details od ON o.order_id = od.order_id
SET  = 'Completed'
WHERE o.total_amount > 1000;

Use CASE statement

-- Update their grades based on the age of students
UPDATE students
SET grade = CASE
    WHEN age < 18 THEN 'primary'
    WHEN age BETWEEN 18 AND 25 THEN 'intermediate'
    ELSE 'advanced'
END;

Use IF statement

-- Update their status based on students' grades
UPDATE students
SET status = IF(score >= 60, 'Pass', 'Failed');

Use the CONCAT function

-- Add after the student's name 'classmate'
UPDATE students
SET name = CONCAT(name, 'classmate');

Use the REPLACE function

-- Put the student's name in 'open' Replace with 'plum'
UPDATE students
SET name = REPLACE(name, 'open', 'plum');

Use COALESCE or IFNULL to process NULL values

-- If the student's grades are NULL，Then set it to 0
UPDATE students
SET score = COALESCE(score, 0);

3. Performance optimization strategy

Using indexes

existWHEREUsing index fields in clauses can significantly speed up data retrieval. Make sure that fields in the update condition have appropriate indexes.

-- Assumptions id Fields have indexes
UPDATE students
SET name = 'Zhang San'
WHERE id = 1;

Batch updates

If you need to update multiple records, you can consider adding multiple recordsUPDATECombine statements into one to reduce transaction overhead.

-- Batch updates of multiple records
UPDATE employees
SET salary = CASE
    WHEN id = 1 THEN 50000
    WHEN id = 2 THEN 60000
    WHEN id = 3 THEN 70000
    ELSE salary
END
WHERE id IN (1, 2, 3);

Avoid full table updates

Try to avoid not bringingWHEREClause ofUPDATEstatement, because this will cause the entire table to be updated and consume a lot of resources.

-- Avoid this writing
UPDATE employees
SET salary = 50000;

Use LIMIT

In some cases, it can be usedLIMITLimit the number of update rows, especially when the update operation may cause lock competition.

-- Limit the number of updated rows
UPDATE employees
SET salary = 50000
WHERE id > 1000
LIMIT 100;

Optimize transactions

For large-batch update operations, you can consider performing updates in batches, and manually submitting transactions after each batch of updates to avoid long-term table locking.

START TRANSACTION;
UPDATE employees
SET salary = 50000
WHERE id BETWEEN 1 AND 1000;
COMMIT;

START TRANSACTION;
UPDATE employees
SET salary = 50000
WHERE id BETWEEN 1001 AND 2000;
COMMIT;

4. Things to note

  • Backup data: It is recommended to back up data before performing large-scale or important update operations.
  • Usage transactions: For complex update operations, it is recommended to use transactions to ensure data consistency and integrity.
  • Performance considerations: When updating large amounts of data, the use of indexes and the impact of locking mechanisms should be considered.
  • Data consistency: Ensure that update operations do not cause data inconsistency or violate business rules.

5. Practical examples

Suppose we have oneemployeesTable, containing the following fields:id, name, salary, department_id. Here are some practical examples:

Update salary for specific employees

-- Will id for 1 的员工的工资设for 60000
UPDATE employees
SET salary = 60000
WHERE id = 1;

Update salary for multiple employees

-- To 10 All employees' wage increases 10%
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;

Update the employee department

-- Will id for 1 的员工的部门设for 20
UPDATE employees
SET department_id = 20
WHERE id = 1;

Departments that update employees using subqueries

-- Will id for 1 的员工Department of设for 'R&D Department' Department of ID
UPDATE employees
SET department_id = (SELECT id FROM departments WHERE name = 'R&D Department')
WHERE id = 1;

Update multiple fields

-- Will id for 1 的员工的名字设for 'Li Si'，工资设for 70000
UPDATE employees
SET name = 'Li Si', salary = 70000
WHERE id = 1;

6. Summary

MySQLUPDATEStatements are an indispensable part of database operations. By using indexes reasonably, batch updates, avoiding full table updates, and usingLIMITAnd optimize transactions can significantly improveUPDATEThe execution efficiency of the statement.

This is the end of this article about UPDATE (update data) in MySQL database. For more information about UPDATE update data related to MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!