How to use UPDATE to update the data of a single table or update the data of multiple tables by joining them - AnalyticDB for MySQL

You can execute the UPDATE statement to update data in a table. 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 ...]

Examples

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 used.

Note

For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL 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

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, set the table that you want to update to 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;