UPDATE
Description
This statement is used to update data in a table.
Limitations and Considerations
Directly updating values in a subquery is not supported, whether it's a single-table or multi-table update. Otherwise, an error will occur. Here is an example:
UPDATE (SELECT * FROM T1) SET C1 = 100;
The result is as follows:
ERROR 1288 (HY000): The target table of the UPDATE is not updatable
Privileges
To execute the UPDATE statement, the current user must have the UPDATE privilege. For more information about seekdb privileges, see seekdb Privilege 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]
Parameter Description
| Parameter | Description |
|---|---|
| hint_options | Optional. Specifies the hint option. For more information about hints, see Optimizer Hint. |
| IGNORE | Optional. If a duplicate key is encountered during an update, this option will ignore the update for that row. |
| table_references | Specifies the list of tables to update. This can be one or more tables. When updating multiple tables, the table names are separated by commas (,). For more information, see table_references below. |
| update_asgn_list | Specifies the columns to update and their corresponding new values. |
| column_name | The name of the column. |
| WHERE where_condition_list | Optional. Specifies the filter conditions, indicating which rows will be updated. For more information about updating specific data based on conditions, see where_condition below. |
| ORDER BY order_expression_list | Optional. Specifies the sort keys for the rows to be updated. This is typically used with the LIMIT clause. For more information about the update order, see order_expression below. |
| LIMIT row_count | Optional. Specifies the number of rows to update. |
table_references
-
table_name: Specifies the name of the table whose data needs to be updated. You can also use thePARTITIONkeyword to specify the partition to update. -
partition_name_list: Specifies the list of partition names whose data needs to be updated. This can be one or more partition names. When updating multiple partitions, the partition names are separated by commas (,).partition_name: Specifies the name of the partition whose data needs to be updated.
Example:
-
Create a table named
tbl1.CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT)
PARTITION BY HASH(col1)
PARTITIONS 5; -
Insert test data into the
tbl1table.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); -
View the data in the
p0andp1partitions of thetbl1table.SELECT * FROM tbl1 PARTITION(p0, p1);The result is as follows:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 5 | A5 | 5 |
| 1 | A1 | 1 |
| 6 | A6 | 6 |
+------+------+------+
3 rows in set (0.012 sec) -
Update the data in the
p0andp1partitions of thetbl1table. Concatenate the value of thecol2column with an underscore and the stringupdate, and then update the value of thecol2column to the concatenated result.UPDATE tbl1 PARTITION(p0, p1)
SET col2 = CONCAT(col2, '_', 'update');The result is as follows:
Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0 -
View the data in the
p0andp1partitions of thetbl1table again.SELECT * FROM tbl1 PARTITION(p0, p1);The result is as follows:
+------+-----------+------+
| col1 | col2 | col3 |
+------+-----------+------+
| 5 | A5_update | 5 |
| 1 | A1_update | 1 |
| 6 | A6_update | 6 |
+------+-----------+------+
3 rows in set (0.013 sec)
where_condition
expression: Specifies the condition expression used to filter the rows to be updated.
Example:
-
View the data in the
tbl1table wherecol1 = 2.SELECT * FROM tbl1 WHERE col1 = 2;The result is as follows:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 2 | A2 | 2 |
+------+------+------+
1 row in set (0.010 sec) -
Update the rows in the
tbl1table wherecol1 = 2. Set the value of thecol2column toupdate A2and the value of thecol3column to 22.UPDATE tbl1
SET col2 = 'update A2',
col3 = 22
WHERE col1 = 2;The result is as follows:
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0 -
View the data in the
tbl1table wherecol1 = 2again.SELECT * FROM tbl1 WHERE col1 = 2;The result is as follows:
+------+-----------+------+
| col1 | col2 | col3 |
+------+-----------+------+
| 2 | update A2 | 22 |
+------+-----------+------+
1 row in set (0.013 sec)
order_expression
expression [ASC | DESC]: Specifies the expression to sort in ascending (ASC, default) or descending (DESC) order.
Example:
-
View the values of the
col1column in thetbl1table where the value is greater than 5, and sort the results in descending order based on the values of thecol3column.SELECT *
FROM tbl1
WHERE col1 > 5
ORDER BY col3 DESC;The result is as follows:
+------+-----------+------+
| col1 | col2 | col3 |
+------+-----------+------+
| 9 | A9 | 9 |
| 8 | A8 | 8 |
| 7 | A7 | 7 |
| 6 | A6_update | 6 |
+------+-----------+------+
4 rows in set (0.018 sec) -
Update the
tbl1table. Multiply the value of thecol3column by 10 for rows where the value of thecol1column is greater than 5, and sort the results in descending order based on the values of thecol3column. Update the top 2 rows.UPDATE tbl1
SET col3 = col3*10
WHERE col1 > 5
ORDER BY col3 DESC
LIMIT 2;The result is as follows:
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0 -
View the data in the
tbl1table again.SELECT *
FROM tbl1
WHERE col1 > 5
ORDER BY col3 DESC;The result is as follows:
+------+-----------+------+
| col1 | col2 | col3 |
+------+-----------+------+
| 9 | A9 | 90 |
| 8 | A8 | 80 |
| 7 | A7 | 7 |
| 6 | A6_update | 6 |
+------+-----------+------+
4 rows in set (0.013 sec)
Example
-
Create a table named
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);
-
Update multiple tables.
-
Update the
col3column in thetbl1table and thecol2column in thetest_tbl1table for rows that satisfy the conditiontbl1.col1 = test_tbl1.col1. Set thecol3column value to100in thetbl1table and thecol2column value to200in thetest_tbl1table.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 (0.001 sec)
Rows matched: 10 Changed: 10 Warnings: 0 -
Query 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 (0.014 sec)
-
-
Use the
IGNOREkeyword to update the data in thetest_tbl1table. If a duplicate key is encountered during the update, the update operation for that row is ignored.-
Update the
test_tbl1table. For rows where thecol1value is greater than 3, increment thecol1value by 1.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: 0 -
Query 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 (0.019 sec)
-
-
Update the values in an updatable view.
-
Create a view named
v1.CREATE VIEW v1 AS SELECT * FROM test_tbl1; -
Update the rows in the
v1view wherev1.col1 = 1. Set thev1.col2value to 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: 0 -
Query the
v1view.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 (0.012 sec)
-
-
Update the values of the partitioning key.
-
Create a table named
test_tbl2.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.INSERT INTO test_tbl2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -
Update the
col2value to 55 for rows in thetest_tbl2table wherecol1 = 4.UPDATE test_tbl2
SET col2 = 55
WHERE col1 = 4;
-