W3Cschool
恭喜您成為首批注冊用戶
獲得88經(jīng)驗值獎勵
該語句用于修改表中的字段值。
UPDATE [hint_options] dml_table_clause
SET update_asgn_list
[WHERE where_condition]
[{ RETURNING | RETURN } returning_exprs [into_clause]]
dml_table_clause:
dml_table_name opt_table_alias
update_asgn_list:
column_name = expr [, ...]
where_condition:
expression
returning_exprs:
projection [, ...]
into_clause:
{ INTO into_var_list | BULK COLLECT INTO into_var_list}
into_var_list:
{ USER_VARIABLE | ref_name } [, ...]
參數(shù) |
描述 |
---|---|
hint_options |
指定 hint 選項。 |
dml_table_clause |
指定修改的表名(基表、可更新視圖、特殊子查詢) |
where_condition |
指定過濾條件。 |
update_asgn_list |
指定更新列表。 |
returning_exprs |
返回修改數(shù)據(jù)后的投影列。 |
into_clause |
將修改數(shù)據(jù)后的投影列插入到指定列表 |
注意
特殊子查詢指的類似于可更新視圖對應(yīng)的子查詢,這類子查詢不應(yīng)該包含復(fù)雜的算子(比如group by/distinct/window function等等)
創(chuàng)建示例表 t1 和 t2。
OceanBase(admin@test)>create table t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.06 sec)
單表更新:將表 t1 中 "t1.c1=1" 對應(yīng)的那一行數(shù)據(jù)的 c2 列值修改為 100。
OceanBase(admin@test)>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
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)
單表更新:直接操作子查詢,將子查詢 中 "v.c1=1" 對應(yīng)的那一行數(shù)據(jù)的 c2 列值修改為 100。
OceanBase(admin@test)>update (select * from t1)v set v.c2 = 100 where v.c1 = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
OceanBase(admin@test)>select * from t1;
+----+------+
| C1 | C2 |
+----+------+
| 1 | 100 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)
單表更新:包含 returning 子句。
OceanBase(admin@test)>update t1 set t1.c2 = 100 where t1.c1 = 1 returning c2;
+------+
| C2 |
+------+
| 100 |
+------+
1 row in set (0.02 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| C1 | C2 |
+----+------+
| 1 | 100 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)
Copyright©2021 w3cschool編程獅|閩ICP備15016281號-3|閩公網(wǎng)安備35020302033924號
違法和不良信息舉報電話:173-0602-2364|舉報郵箱:jubao@eeedong.com
掃描二維碼
下載編程獅App
編程獅公眾號
聯(lián)系方式:
更多建議: