Description

Update records in a database table, modifying specified field values to new values.

Syntax

UPDATE target_table
       SET column_name1 = new_value1 [ , column_name2 = new_value2 , ... ]
 [ WHERE condition ]
[ORDER BY ...] 
[LIMIT row_count]

Required Parameters

• target_table: The name of the target table where records need to be updated.
• column_name: The name of the column that needs to be updated.
• new_value: The new value, which can be a constant, variable, function call, arithmetic operator, string operation, etc. Subqueries are supported.

Optional Parameters

• condition: The condition for the update operation, used to specify which records need to be updated. The condition can include subqueries and expressions.

• ORDER BY ...: Update after sorting by the specified columns:

  • Typically used in conjunction with LIMIT to control the order of updates.
  • Suitable for batch updates or processing data by priority (e.g., processing the most recent records first).

• LIMIT row_count: Limit the maximum number of rows to be updated:

  • Commonly used for batch-updating large tables to avoid table locks or excessively long transactions.
  • Should be used with ORDER BY to ensure deterministic update order.

Example

Example 1: Update a single field value Suppose we have a table named employees that contains the ID, name, and salary of employees. Now we want to increase the salary of the employee with ID 1 by 1000 yuan.

UPDATE employees
SET salary = salary + 1000
WHERE id = 1;

Example 2: Updating Multiple Field Values Simultaneously Continuing with the employees table, now we want to increase the salary of the employee with ID 1 by 1000 yuan and change the position from "Developer" to "Senior Developer".

UPDATE employees
SET salary = salary + 1000, position = 'Senior Developer'
WHERE id = 1;

Example 3: Using Subqueries to Update Field Values Assuming we have a table named orders that contains order ID, customer ID, and order amount. We also have a table named customers that contains customer ID, name, and membership level. Now we want to increase the order amount of all VIP customers by 10%.

UPDATE orders
SET amount = amount * 1.1
WHERE customer_id IN (
  SELECT id
  FROM customers
  WHERE membership_level = 'VIP'
);

Precautions

• When performing update operations, please ensure that the WHERE clause is correct to avoid mistakenly updating records that should not be updated.
• Before performing update operations in the production environment, it is recommended to first verify in the testing environment.