tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Usage specification recommendations
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
DocumentaçãoTDSQL BoundlessDeveloper GuideDeveloper Guide (MySQL Compatibility Mode)Data WritingData Update

Data Update

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 2026-04-03 15:58:02
This article describes how to use SQL statements to update data in TDSQL Boundless databases, including single-table updates, multi-table updates, conditional updates, using INSERT...ON DUPLICATE KEY UPDATE to achieve "update if exists" operations, and best practices for batch updates.

Prerequisites

Before data update operations are performed, ensure the following conditions are met:
The TDSQL Boundless instance has been created and connected. See Connecting to Databases.
The target databases and tables have been created, and the tables contain data that require updating.
The current user has UPDATE permission on the target table.

Use UPDATE to update data

UPDATE statements are used to modify column values in existing table records. The following describes the basic usage and common scenarios of UPDATE statements.

Basic Syntax

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;
Parameter description
Parameter
Description
table_name
The target table name that requires updating
SET clause
Specify the columns to be updated and their new values, separating multiple columns with commas.
WHERE clause
Specify the update conditions; only rows that meet the conditions will be updated.
Note:
If the WHERE clause is omitted, the UPDATE statement will update all rows in the table. When performing UPDATE operations in a production environment, be sure to specify WHERE conditions to avoid accidentally updating all table data.

Updating a single column value

The following example updates the salary of the employee with ID 1 to 20000.
UPDATE employees SET salary = 20000.00 WHERE id = 1;
After successful execution, the returned results are as follows:
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Update multiple columns simultaneously

An UPDATE statement can update multiple columns simultaneously.
UPDATE employees
SET salary = 22000.00, department = 'Architecture Department'
WHERE id = 1;

Use expressions to update

SET clause can use expressions to calculate new values. For example, increasing the salary of all employees in the Technical Department by 10%.
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Technical Department';

Use UPDATE...ORDER BY...LIMIT to limit updates

UPDATE statements support ORDER BY and LIMIT clauses, which can limit the number and order of updated rows. This syntax is commonly used for batch updates or scenarios where only the first few rows need to be updated.
Syntax Format
UPDATE table_name
SET column1 = new_value1
WHERE condition
ORDER BY sort_column
LIMIT row_count;
Example
The following example adjusts the salary of the 3 employees with the lowest salaries to 12000.
UPDATE employees
SET salary = 12000.00
ORDER BY salary ASC
LIMIT 3;
Note:
Using ORDER BY with LIMIT ensures the determinism of update operations. When batch updating massive data, it is recommended to use ORDER BY primary key with LIMIT for batch processing.

multi-table update

TDSQL Boundless supports updating data in multiple tables within a single UPDATE statement, typically used with the JOIN clause.

Basic Syntax

UPDATE table1
JOIN table2 ON table1.column = table2.column
SET table1.column1 = new_value1, table2.column2 = new_value2
WHERE condition;

Example

The following example updates the salaries of employees in corresponding departments based on the salary adjustment ratios in the department table.
-- Create department salary adjustment table
CREATE TABLE department_raise (
    department VARCHAR(50) PRIMARY KEY,
    raise_rate DECIMAL(4, 2)
);

INSERT INTO department_raise VALUES ('Technical Department', 1.15), ('Product Department', 1.10), ('Marketing Department', 1.08);

-- Use multi-table JOIN to update employee salaries
UPDATE employees e
JOIN department_raise d ON e.department = d.department
SET e.salary = e.salary * d.raise_rate;

Use INSERT...ON DUPLICATE KEY UPDATE

The INSERT...ON DUPLICATE KEY UPDATE statement performs the UPDATE operation when a primary key or unique key conflict is encountered during data insertion. This syntax is suitable for "insert if not exists, update if exists" business scenarios.

Basic Syntax

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)
ON DUPLICATE KEY UPDATE column1 = new_value1, column2 = new_value2, ...;

Example

The following example implements the insertion or update logic for user ratings: if a user has not rated a certain book, new records are inserted; otherwise, the rating and rating time are updated.
-- Create rating table
CREATE TABLE book_ratings (
    user_id INT,
    book_id INT,
    rating TINYINT,
    rated_at DATETIME,
    PRIMARY KEY (user_id, book_id)
);

-- Insert or update ratings
INSERT INTO book_ratings (user_id, book_id, rating, rated_at)
VALUES (1001, 50, 4, NOW())
ON DUPLICATE KEY UPDATE rating = VALUES(rating), rated_at = VALUES(rated_at);
Note:
It is recommended to use this syntax only on tables containing a single unique key. When there are multiple unique keys in the table, it may match an unexpected unique key, resulting in updating non-target rows.
The VALUES() function is used to reference the values to be inserted in the INSERT clause. In MySQL 8.0.20 and later versions, VALUES() has been marked as deprecated in this scenario, and it is recommended to use aliases instead.

Batch Update

When an update is performed on a large volume of data (such as tens of thousands of rows or more), performing extensive UPDATE operations within a single transaction may lead to issues such as oversized transactions and prolonged lock holding times. It is advisable to split large-scale updates into multiple smaller batches.

Basic approach to batch updates

1. Use SELECT to query the primary key range of records to be updated.
2. Use UPDATE...WHERE...LIMIT to update data in batches.
3. Repeat step 2 within the loop until all data updates are completed.

Example

The following example updates all employees' salaries by multiplying the original value by 2, performing the update in batches.
1. Add flag column (optional)
To avoid duplicate updates after an abnormal script interruption, a flag column can be added to record the update status.
ALTER TABLE employees ADD COLUMN salary_updated TINYINT DEFAULT 0;
2. Perform updates in batches
-- Update 1000 rows each time
UPDATE employees
SET salary = salary * 2, salary_updated = 1
WHERE salary_updated = 0
LIMIT 1000;
Execute the above statement repeatedly until the returned Rows matched is 0.
3. Clean the flag column
ALTER TABLE employees DROP COLUMN salary_updated;
Note:
When batch updates are performed, it is recommended to update 1000 - 10000 rows of data per batch. Pause appropriately (such as 100ms) between batches to reduce the impact on online services.

UPDATE Best Practices

When the UPDATE statement is used, it is recommended to follow the following best practices.

Always use the WHERE clause

When UPDATE operations are performed, a WHERE clause must be specified to limit the update scope. An UPDATE statement without a WHERE clause will update all table data, which may cause serious data problems.

Prioritize using primary keys or indexed columns as filtering conditions

Using the primary key or indexed columns in the WHERE clause can avoid full table scans and improve update efficiency.
Recommended practice
UPDATE employees SET salary = 20000.00 WHERE id = 1;
Not recommended practice
UPDATE employees SET salary = 20000.00 WHERE name = 'Zhang San';
If the name column has no index, the statement will perform a full table scan.

Avoid modifying primary keys or unique keys during updates

Modifying primary keys or unique keys may trigger additional index maintenance operations, impacting performance. If you need to change a primary key value, it is recommended to delete the old record first and then insert a new one.

Perform large-scale updates in batches

When tens of thousands of rows or more are updated, it is recommended to adopt a batch update policy. A single large transaction may lead to the following issues:
Lock wait timeout (tdsql_lock_wait_timeout).
Excessively large transaction logs affecting database performance.
Long-term holding of row-level locks, blocking other business operations.

Verify the affected scope before updating

Before performing an UPDATE operation, it is recommended to first use a SELECT statement with identical conditions to query the data to be updated, ensuring the update scope meets expectations.
-- First, view the data to be updated.
SELECT id, name, salary FROM employees WHERE department = 'Technical Department';

-- Confirm the correctness before executing the update.
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Technical Department';

Limitations

When using the UPDATE statement, note the following considerations:
UPDATE statements are automatically committed in the default autocommit = 1 mode. To enable rollback, please enable transactions before executing the UPDATE operation.
When triggers exist on a table, the UPDATE operation will trigger the corresponding BEFORE UPDATE and AFTER UPDATE triggers.
UPDATE operations apply row-level locks to matched rows. In high-concurrency scenarios, if multiple transactions simultaneously update the same rows, lock waits or deadlocks may occur.
If the SET value of the UPDATE is the same as the current value, the number of affected rows (Changed) is 0, but the number of matched rows (Rows matched) is still counted.
In distributed transaction scenarios, cross-shard UPDATE operations use the Two-Phase Commit (2PC) protocol to ensure data consistency.
Anterior: Data InsertionPróximo: Data Deletion

Ajuda e Suporte

Esta página foi útil?

comentários