UPDATE |V3.2.4|OceanBase Database| docs|Distributed Database

UPDATE

Last Updated：2023-10-24 09:23:03 Updated

Purpose

You can use this statement to modify the field values in a table.

Syntax

UPDATE [IGNORE] table_references
    SET update_asgn_list
    [WHERE where_condition] 
    [ORDER BY order_list]
    [LIMIT row_count];

table_references:
    table_name [PARTITION (partition_name,...)] [, ...]

update_asgn_list:
    column_name = expr [, ...]

order_list: 
    column_name [ASCDESC] [, column_name [ASCDESC]...]

Parameters

Parameter	Description
IGNORE	Ignores errors that occur during the execution of an `INSERT` statement.
table_references	The name of the table to be modified. Multiple table names must be separated with commas (,).
where_condition	The filter condition.
row_count	The maximum number of rows.
table_name	The name of the table to which the data is inserted.
partition_name	The name of the partition to which the data is inserted.
column_name	The name of the column.
ASC	Sorts column names in ascending order.
DESC	Sorts column names in descending order.

Notes

Direct UPDATE operations on subqueries are not supported for either a single table or multiple tables. Otherwise, an error occurs. Example:

obclient> UPDATE (SELECT * FROM T1) SET C1 = 100;
ERROR 1288 (HY000): The target table of the UPDATE is not updatable

Examples

Create tables t1 and t2.

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.16 sec)
obclient> SELECT * FROM t1;
+----+------+
 c1  c2   
+----+------+
  1     1 
  2     2 
  3     3 
  4     4 
+----+------+
4 rows in set (0.06 sec)

obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.19 sec)
obclient> SELECT * FROM t2;
+----+------+
 c1  c2   
+----+------+
  5     5 
  1     1 
  2     2 
  3     3 
+----+------+
4 rows in set (0.02 sec)

For the row where t1.c1 = 1 in table t1, set its value in column c2 to 100.

obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
 c1  c2   
+----+------+
  1   100 
  2     2 
  3     3 
  4     4 
+----+------+
4 rows in set (0.01 sec)

For the first two rows sorted by column c2 in table t1, set their values in column c2 to 100.

obclient> UPDATE t1 SET t1.c2 = 100 ORDER BY c2 LIMIT 2;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
 c1  c2   
+----+------+
  1   100 
  2   100 
  3     3 
  4     4 
+----+------+
4 rows in set (0.01 sec)

In partition p2 of table t2, for rows where t2.c1 > 2, set their values in column c2 to 100.

obclient> UPDATE t2 PARTITION(p2) SET t2.c2 = 100 WHERE t2.c1 > 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t2;
+----+------+
 c1  c2   
+----+------+
  5     5 
  1     1 
  2     2 
  3   100 
+----+------+
4 rows in set (0.06 sec)

Update multiple tables. For rows where t1.c1 = t2.c1, set their values in column c2 of table t1 to 100 and set their values in column c2 of table t2 to 200.

obclient> UPDATE t1,t2 SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6  Changed: 6  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
 c1  c2   
+----+------+
  1   100 
  2   100 
  3   100 
  4     4 
+----+------+
4 rows in set (0.00 sec)

obclient> SELECT * FROM t2;
+----+------+
 c1  c2   
+----+------+
  5     5 
  1   200 
  2   200 
  3   200 
+----+------+
4 rows in set (0.01 sec)

Update multiple tables. For rows where t1.c1 = t2.c1, set their values in column c2 in partition p2 of table t1 to 100 and set their values in column c2 in partition p2 of table t2 to 200.

obclient> UPDATE t1,t2 PARTITION(p2) SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
 c1  c2   
+----+------+
  1   100 
  2   100 
  3   100 
  4     4 
+----+------+
4 rows in set (0.01 sec)

obclient> SELECT * FROM t2;
+----+------+
 c1  c2   
+----+------+
  5     5 
  1   200 
  2   200 
  3   200 
+----+------+
4 rows in set (0.01 sec)

Update an updatable view v.

obclient> CREATE VIEW v AS SELECT * FROM t1;
Query OK, 0 rows affected (0.07 sec)

obclient> UPDATE v SET v.c2 = 100 WHERE v.c1 = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM v;
+----+------+
 c1  c2   
+----+------+
  1   100 
  2     2 
  3     3 
  4     4 
+----+------+
4 rows in set (0.01 sec)