You can execute the UPDATE
statement to update data. AnalyticDB for MySQL allows you to update the data of a single table or multiple tables.
Usage notes
The table on which you execute the
UPDATE
statement must have a primary key.You cannot update the data of primary key columns.
The ORDER BY and LIMIT clauses are supported only for single-table updates.
Update a single table
Syntax
UPDATE table_name
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT ...]
Example
Change the name of the customer whose ID is 2369
in the customer table to Claire.
UPDATE
customer
SET
customer_name ='Claire'
WHERE
customer_id ='2369';
Update multiple tables
Prerequisites
An AnalyticDB for MySQL cluster of V3.1.6.4 or later is created.
For information about how to view the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster? To update the minor version of a cluster, contact technical support.
Syntax
UPDATE Table_name1
[INNER JOIN | LEFT JOIN] Table_name2 ON Table_name1.C1 = Table_name2.C1
SET assignment_list
[WHERE where_condition]
Usage notes
You can update only two tables for multi-table updates.
The ORDER BY and LIMIT clauses are not supported for multi-table updates.
If you want to update the data of multiple tables, you can execute one UPDATE statement only on a single table.
When you update the data of multiple tables, place the table that you want to update in the first place. For example, execute the following statement to change the
customer ID
in thecustomer
table to 1:UPDATE customer LEFT JOIN new_customer ON customer.customer_name = new_customer.customer_name SET customer.customer_id = '1';
Examples
Execute the
UPDATE
statement that containsLEFT JOIN
.Change the age of the customer whose ID is
2369
in the customer table to 42.UPDATE customer LEFT JOIN new_customer ON customer.customer_id = new_customer.customer_id SET customer.customer_age = 42 WHERE new_customer.customer_id = '2369';
Execute the
UPDATE
statement that containsINNER JOIN
.Change the customer ID in the customer table to 2369.
UPDATE customer INNER JOIN new_customer ON customer.customer_name = new_customer.customer_name SET customer.customer_id = '2369';
Change the customer_name field of the customer table to the customer_name field of the new_customer table.
UPDATE customer INNER JOIN new_customer ON customer.customer_id = new_customer.customer_id SET customer.customer_name = new_customer.customer_name;