Update Single and Multi-Table Data with DML in AnalyticDB MySQL - AnalyticDB

Use the UPDATE statement to modify data in a single table or multiple tables in AnalyticDB for MySQL.

Limitations

The target table must have a primary key.
Primary key columns cannot be updated.
ORDER BY and LIMIT clauses are supported only for single-table updates.
Multi-table updates support joining at most two tables.
Each UPDATE statement can target only one table. The table to be modified must appear first in the statement.
Multi-table UPDATE requires AnalyticDB for MySQL V3.1.6.4 or later. To check or upgrade your cluster version, go to the Cluster Information page in the AnalyticDB for MySQL console and see the Configuration Information section. For instructions on updating the minor version, see Update the minor version of a cluster.

Update a single table

Syntax

UPDATE table_name
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT ...]

Update a row by ID

Change the name of customer 2369 to Claire:

UPDATE customer
SET customer_name = 'Claire'
WHERE customer_id = '2369';

To verify the update:

SELECT customer_id, customer_name
FROM customer
WHERE customer_id = '2369';

Update multiple tables

Use a JOIN clause to read values from a second table and write updates to the first table.

Syntax

UPDATE table_name1
[INNER JOIN | LEFT JOIN] table_name2 ON table_name1.column = table_name2.column
SET assignment_list
[WHERE where_condition]

Place the table to be updated first in the UPDATE statement.

Update a column using LEFT JOIN

Change the age of customer 2369 using a matching record in new_customer:

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';

To verify the update:

SELECT customer_id, customer_age
FROM customer
WHERE customer_id = '2369';

Update a column using INNER JOIN

Copy customer_name from new_customer to customer based on a matching customer_id:

UPDATE customer
    INNER JOIN new_customer ON customer.customer_id = new_customer.customer_id
SET customer.customer_name = new_customer.customer_name;

Set customer_id to 2369 for all rows where the customer name matches between customer and new_customer:

UPDATE customer
    INNER JOIN new_customer ON customer.customer_name = new_customer.customer_name
SET customer.customer_id = '2369';

Set a column to a fixed value

Set customer_id to 1 for all rows in customer where the customer name matches a row in new_customer:

UPDATE customer
    LEFT JOIN new_customer ON customer.customer_name = new_customer.customer_name
SET customer.customer_id = '1';

Usage notes

High-frequency updates with INSERT ON DUPLICATE KEY UPDATE

Running INSERT ON DUPLICATE KEY UPDATE at more than 100 QPS or on large data volumes can significantly increase CPU utilization. For high-throughput upsert workloads, use REPLACE INTO instead. For more information, see REPLACE INTO.