UPDATE

描述

该语句用于更新表中的数据。

使用限制及注意事项

不管是多表更新还是单表更新都不支持直接对子查询进行更新值操作，否则会报错。示例如下：

UPDATE (SELECT * FROM T1) SET C1 = 100;

返回结果如下：

ERROR 1288 (HY000): The target table  of the UPDATE is not updatable

权限要求

执行 UPDATE 语句，需要当前用户拥有 UPDATE 权限。有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

语法

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]

参数解释

table_references

• table_name：指定需要更新数据的表名。并且也可以通过 PARTITION 关键字指定分区更新数据。

• partition_name_list：指定需要更新数据的分区名称的列表，可以是一个或多个分区名称。更新多个分区时，分区名间以英文逗号（,）分隔。

  • partition_name：表示需要更新数据的分区名。

示例如下：

1. 创建表 tbl1。

   CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT)
     PARTITION BY HASH(col1)
     PARTITIONS 5;
   

2. 向表 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);
   

3. 查看表 tbl1 中 p0 和 p1 分区的数据。

   SELECT * FROM tbl1 PARTITION(p0, p1);
   

   返回结果如下：

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    5 | A5   |    5 |
   |    1 | A1   |    1 |
   |    6 | A6   |    6 |
   +------+------+------+
   3 rows in set
   

4. 更新表 tbl1 中 p0 和 p1 分区的数据，将 col2 列的值与下划线和字符串 update 拼接在一起，然后更新 col2 列的值为拼接后的结果。

   UPDATE tbl1 PARTITION(p0, p1)
     SET col2 = CONCAT(col2, '_', 'update');
   

   返回结果如下：

   Query OK, 3 rows affected
   Rows matched: 3  Changed: 3  Warnings: 0
   

5. 再次查看表 tbl1 中 p0 和 p1 分区的数据。

   SELECT * FROM tbl1 PARTITION(p0, p1);
   

   返回结果如下：

   +------+-----------+------+
   | col1 | col2      | col3 |
   +------+-----------+------+
   |    5 | A5_update |    5 |
   |    1 | A1_update |    1 |
   |    6 | A6_update |    6 |
   +------+-----------+------+
   3 rows in set
   

where_condition

expression：指定可用于过滤要更新的行的条件表达式。

示例如下：

1. 查看表 tbl1 中 col1 = 2 的数据。

   SELECT * FROM tbl1 WHERE col1 = 2;
   

   返回结果如下：

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    2 | A2   |    2 |
   +------+------+------+
   1 row in set
   

2. 更新表 tbl1 中 col1 = 2 的行，将 col2 的值设为 update A2，将 col3 的值设为 22。

   UPDATE tbl1
     SET col2 = 'update A2',
         col3 = 22
     WHERE col1 = 2;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

3. 再次查看表 tbl1 中 col1 = 2 的数据。

   SELECT * FROM tbl1 WHERE col1 = 2;
   

   返回结果如下：

   +------+-----------+------+
   | col1 | col2      | col3 |
   +------+-----------+------+
   |    2 | update A2 |   22 |
   +------+-----------+------+
   1 row in set
   

order_expression

expression [ASC | DESC]：指定按升序（ASC，默认值）或降序（DESC）排序的表达式。

示例如下：

1. 查看表 tbl1 中列 col1 的值大于 5，并按照 col3 列的值按降序（从大到小）对结果进行排序。

   SELECT *
     FROM tbl1
     WHERE col1 > 5
     ORDER BY col3 DESC;
   

   返回结果如下：

   +------+-----------+------+
   | col1 | col2      | col3 |
   +------+-----------+------+
   |    9 | A9        |    9 |
   |    8 | A8        |    8 |
   |    7 | A7        |    7 |
   |    6 | A6_update |    6 |
   +------+-----------+------+
   4 rows in set
   

2. 对 tbl1 表进行更新操作，将满足条件 col1 大于 5 的行中的 col3 列的值乘以 10，并按照 col3 降序排序，取出前 2 行进行更新。

   UPDATE tbl1
     SET col3 = col3*10
     WHERE col1 > 5
     ORDER BY col3 DESC
     LIMIT 2;
   

   返回结果如下：

   Query OK, 2 rows affected
   Rows matched: 2  Changed: 2  Warnings: 0
   

3. 再次查看表 tbl1 中的数据。

   SELECT *
     FROM tbl1
     WHERE col1 > 5
     ORDER BY col3 DESC;
   

   返回结果如下：

   +------+-----------+------+
   | col1 | col2      | col3 |
   +------+-----------+------+
   |    9 | A9        |   90 |
   |    8 | A8        |   80 |
   |    7 | A7        |    7 |
   |    6 | A6_update |    6 |
   +------+-----------+------+
   4 rows in set
   

示例

1. 创建示例表 test_tbl1。

   CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT);
   

2. 向表 test_tbl1 中插入测试数据。

   INSERT INTO test_tbl1 VALUES
     (1, 1),(2, 2),(3, 3),
     (4, 4),(5, 5);
   

• 修改多个表。

  1. 将 tbl1 表和 test_tbl1 表中满足tbl1.col1 = test_tbl1.col1 对应行的数据 tbl1 表中的 col3 列值修改为 100，test_tbl1 表中的 col2 列值修改为 200。

     UPDATE test_tbl1, tbl1
       SET tbl1.col3 = 100,
           test_tbl1.col2 = 200
       WHERE tbl1.col1 = test_tbl1.col1;
     

     返回结果如下：

     Query OK, 10 rows affected
     Rows matched: 10  Changed: 10  Warnings: 0
     

  2. 查看表 test_tbl1 的数据。

     SELECT * FROM test_tbl1;
     

     返回结果如下：

     +------+------+
     | col1 | col2 |
     +------+------+
     |    1 |  200 |
     |    2 |  200 |
     |    3 |  200 |
     |    4 |  200 |
     |    5 |  200 |
     +------+------+
     5 rows in set
     

• 使用 IGNORE 关键字更新测试表 test_tbl1 中的数据，更新中出现了重复键的情况，就会忽略该行的更新操作。

  1. 对 test_tbl1 表进行更新操作，将满足条件 col1 大于 3 的行中 col1 值将会加 1。

     UPDATE IGNORE test_tbl1
       SET col1 = col1 + 1
       WHERE col1 > 3;
     

     返回结果如下：

     Query OK, 1 row affected
     Rows matched: 2  Changed: 1  Warnings: 0
     

  2. 查看表 test_tbl1 的数据。

     SELECT * FROM test_tbl1;
     

     返回结果如下：

     +------+------+
     | col1 | col2 |
     +------+------+
     |    1 |  200 |
     |    2 |  200 |
     |    3 |  200 |
     |    4 |  200 |
     |    6 |  200 |
     +------+------+
     5 rows in set
     

• 对可更新视图进行更新值。

  1. 创建视图 v1。

     CREATE VIEW v1 AS SELECT * FROM test_tbl1;
     

  2. 更新 v1 中符合条件 v1.col1 = 1 的行，将 v1.col2 的值设置为 100。

     UPDATE v1
       SET v1.col2 = 100
       WHERE v1.col1 = 1;
     

     返回结果如下：

     Query OK, 1 row affected
     Rows matched: 1  Changed: 1  Warnings: 0
     

  3. 查看表 v1 的数据。

     SELECT * FROM v1;
     

     返回结果如下：

     +------+------+
     | col1 | col2 |
     +------+------+
     |    1 |  100 |
     |    2 |  200 |
     |    3 |  200 |
     |    4 |  200 |
     |    6 |  200 |
     +------+------+
     5 rows in set
     

• 更新分区键的值。

  1. 创建表 test_tbl2。

     obclient> CREATE TABLE test_tbl2(col1 INT, col2 INT, PRIMARY KEY(col1, col2))
         PARTITION BY HASH(col2) PARTITIONS 4;
     

  2. 向表 test_tbl2 中插入测试数据。

     obclient> INSERT INTO test_tbl2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
     

  3. 更新表 test_tbl2 中 col1 = 4 的行中 col2 的值为 55。

     obclient> UPDATE test_tbl2
         SET col2 = 55
         WHERE col1 = 4;
     

相关文档

更新数据