Purpose
This statement is used to update data in a table.
Limitations and considerations
Neither multi-table updates nor single-table updates support directly updating values in subqueries; otherwise, an error will be reported. Example:
UPDATE (SELECT * FROM T1) SET C1 = 100;
The return result is as follows:
ERROR 1288 (HY000): The target table of the UPDATE is not updatable
Privilege requirements
To execute the UPDATE statement, the current user must have the UPDATE privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
UPDATE [hint_options] [IGNORE] table_references
SET update_asgn_list
[WHERE where_condition_list]
[ORDER BY order_expression_list]
[LIMIT row_count];
table_references:
table_name [PARTITION (partition_name_list)] [, table_name [PARTITION (partition_name_list)] ...]
partition_name_list:
partition_name [, partition_name ...]
update_asgn_list:
column_name = expr [, column_name = expr ...]
where_condition_list:
where_condition [, where_condition ...]
where_condition:
expression
order_expression_list:
order_expression [, order_expression ...]
order_expression:
expression [ASC | DESC]
Parameters
Parameters |
Description |
|---|---|
| hint_options | Optional. Specifies the hint Options. For more information about hints, see Optimizer hint. |
| IGNORE | Optional. If a duplicate key is encountered during the update operation, the update for that row will be ignored. |
| table_references | Specify the list of table names to be updated. You can update one or more tables. Separate multiple table names with commas (,) separator. For more information, see table_references. |
| update_asgn_list | Specify the column to be updated and its corresponding new value. |
| column_name | Column name. |
| WHERE where_condition_list | Optional. Specifies the filter condition list, which identifies the rows to be updated. For more information about updating data based on specific conditions, see where_condition. |
| ORDER BY order_expression_list | Optional. Specifies the list of sort keys for the rows to be updated. Typically used with LIMIT can be used together. For details about the update order, see order_expression. |
| LIMIT row_count | Optional. Specifies the number of rows to be updated. |
table_references
table_name: The name of the table whose data needs to be updated. You can also use thePARTITIONkeyword to specify the partition for data update.partition_name_list: The list of partition names whose data needs to be updated. You can specify one or more partition names. Separate multiple partition names with commas (,).partition_name: the name of the partition whose data needs to be updated.
Example:
Create table
tbl1.CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT) PARTITION BY HASH(col1) PARTITIONS 5;Insert test data into table
tbl1.INSERT INTO tbl1 VALUES (1, 'A1', 1),(2, 'A2', 2),(3, 'A3', 3), (4, 'A4', 4),(5, 'A5', 5),(6, 'A6', 6), (7, 'A7', 7),(8, 'A8', 8),(9, 'A9', 9);Query the data in partitions
p0andp1of tabletbl1.SELECT * FROM tbl1 PARTITION(p0, p1);The return result is as follows:
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 5 | A5 | 5 | | 1 | A1 | 1 | | 6 | A6 | 6 | +------+------+------+ 3 rows in setUpdate the data in partitions
p0andp1of tabletbl1. Concatenate the value in columncol2with an underscore and the stringupdate, and then update the value in columncol2with the concatenated result.UPDATE tbl1 PARTITION(p0, p1) SET col2 = CONCAT(col2, '_', 'update');The return result is as follows:
Query OK, 3 rows affected Rows matched: 3 Changed: 3 Warnings: 0View the data in partitions
p0andp1of tabletbl1again.SELECT * FROM tbl1 PARTITION(p0, p1);The return result is as follows:
+------+-----------+------+ | col1 | col2 | col3 | +------+-----------+------+ | 5 | A5_update | 5 | | 1 | A1_update | 1 | | 6 | A6_update | 6 | +------+-----------+------+ 3 rows in set
where_condition
expression: Specifies the conditional expression that can be used to filter the rows to be updated.
Example:
Query the data where
col1 = 2in tabletbl1.SELECT * FROM tbl1 WHERE col1 = 2;The return result is as follows:
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 2 | A2 | 2 | +------+------+------+ 1 row in setUpdate the row where
col1 = 2in tabletbl1, set the value ofcol2toupdate A2, and set the value ofcol3to 22.UPDATE tbl1 SET col2 = 'update A2', col3 = 22 WHERE col1 = 2;The return result is as follows:
Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0Check the data for
col1 = 2in tabletbl1again.SELECT * FROM tbl1 WHERE col1 = 2;The return result is as follows:
+------+-----------+------+ | col1 | col2 | col3 | +------+-----------+------+ | 2 | update A2 | 22 | +------+-----------+------+ 1 row in set
order_expression
expression [ASC | DESC]: Specifies the expression for sorting in ascending order (ASC, the default value) or descending order (DESC).
Example:
Query the values in column
col1of tabletbl1for which the value is greater than 5, and sort the query results in descending order by the values in columncol3.SELECT * FROM tbl1 WHERE col1 > 5 ORDER BY col3 DESC;The return result is as follows:
+------+-----------+------+ | col1 | col2 | col3 | +------+-----------+------+ | 9 | A9 | 9 | | 8 | A8 | 8 | | 7 | A7 | 7 | | 6 | A6_update | 6 | +------+-----------+------+ 4 rows in setUpdate the
tbl1table by multiplying the values in thecol3column by 10 for rows wherecol1is greater than 5. Then, sort the rows in descending order bycol3and update the first two rows.UPDATE tbl1 SET col3 = col3*10 WHERE col1 > 5 ORDER BY col3 DESC LIMIT 2;The return result is as follows:
Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0View the data in table
tbl1again.SELECT * FROM tbl1 WHERE col1 > 5 ORDER BY col3 DESC;The return result is as follows:
+------+-----------+------+ | col1 | col2 | col3 | +------+-----------+------+ | 9 | A9 | 90 | | 8 | A8 | 80 | | 7 | A7 | 7 | | 6 | A6_update | 6 | +------+-----------+------+ 4 rows in set
Examples
Create the sample table
test_tbl1.CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT);Insert test data into the
test_tbl1table.INSERT INTO test_tbl1 VALUES (1, 1),(2, 2),(3, 3), (4, 4),(5, 5);
Modify multiple tables.
For the rows that satisfy
tbl1.col1 = test_tbl1.col1in tablestbl1andtest_tbl1, update the value of columncol3in tabletbl1to100and the value of columncol2in tabletest_tbl1to200.UPDATE test_tbl1, tbl1 SET tbl1.col3 = 100, test_tbl1.col2 = 200 WHERE tbl1.col1 = test_tbl1.col1;The return result is as follows:
Query OK, 10 rows affected Rows matched: 10 Changed: 10 Warnings: 0View the data in the
test_tbl1table.SELECT * FROM test_tbl1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 200 | | 2 | 200 | | 3 | 200 | | 4 | 200 | | 5 | 200 | +------+------+ 5 rows in set
Updates data in the
test_tbl1table using theIGNOREkeyword. If a duplicate key is encountered during the update, the update operation for that row is ignored.Update the
test_tbl1table by incrementing the value ofcol1by 1 for rows wherecol1is greater than 3.UPDATE IGNORE test_tbl1 SET col1 = col1 + 1 WHERE col1 > 3;The return result is as follows:
Query OK, 1 row affected Rows matched: 2 Changed: 1 Warnings: 0View the data in the
test_tbl1table.SELECT * FROM test_tbl1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 200 | | 2 | 200 | | 3 | 200 | | 4 | 200 | | 6 | 200 | +------+------+ 5 rows in set
Updates the values in an updatable view.
Create view
v1.CREATE VIEW v1 AS SELECT * FROM test_tbl1;Update the row that meets the condition
v1.col1 = 1inv1and set the value ofv1.col2to 100.UPDATE v1 SET v1.col2 = 100 WHERE v1.col1 = 1;The return result is as follows:
Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0View the data in table
v1.SELECT * FROM v1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 100 | | 2 | 200 | | 3 | 200 | | 4 | 200 | | 6 | 200 | +------+------+ 5 rows in set
Updates the value of a partitioning key.
Create the table
test_tbl2.obclient> CREATE TABLE test_tbl2(col1 INT, col2 INT, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col2) PARTITIONS 4;Insert test data into the
test_tbl2table.obclient> INSERT INTO test_tbl2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);Update the value of
col2to 55 in the row wherecol1 = 4in thetest_tbl2table.obclient> UPDATE test_tbl2 SET col2 = 55 WHERE col1 = 4;