Use the UPDATE statement to update the data of a single table or update the data of multiple tables by joining them - AnalyticDB

You can execute the UPDATE statement to update the data of a single table or multiple tables in AnalyticDB for MySQL.

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.
You can join up to two tables.
The ORDER BY and LIMIT clauses are supported only for single-table updates.
If you execute the INSERT ON DUPLICATE KEY UPDATE statement to update a large amount of data or update data at a high frequency (more than 100 QPS), the CPU utilization may significantly increase. We recommend that you execute the REPLACE INTO statement to batch update data. For more information, see REPLACE INTO.

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

Supported versions

AnalyticDB for MySQL clusters of V3.1.6.4 or later.

Note

To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

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

The ORDER BY and LIMIT clauses are not supported for multi-table updates.
When you update the data of multiple tables, you can join up to two tables.
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 the customer 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 contains LEFT 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 contains INNER 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;