All Products
Search
Document Center

AnalyticDB:UPDATE

Last Updated:Aug 12, 2024

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.

Note

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