This is an automated email from the ASF dual-hosted git repository.
luzhijing pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new f8a4c95af267 [typo](docs) update/delete supports CTE (#492)
f8a4c95af267 is described below
commit f8a4c95af26749fbffd2b889257c1cce4916f38b
Author: yagagagaga <[email protected]>
AuthorDate: Tue Apr 2 15:13:24 2024 +0800
[typo](docs) update/delete supports CTE (#492)
---
.../Manipulation/DELETE.md | 60 +++++++++++++------
.../Manipulation/UPDATE.md | 68 +++++++++++++---------
.../Manipulation/DELETE.md | 60 +++++++++++++------
.../Manipulation/UPDATE.md | 68 +++++++++++++---------
.../Manipulation/DELETE.md | 60 +++++++++++++------
.../Manipulation/UPDATE.md | 68 +++++++++++++---------
.../Manipulation/DELETE.md | 60 +++++++++++++------
.../Manipulation/UPDATE.md | 68 +++++++++++++---------
8 files changed, 336 insertions(+), 176 deletions(-)
diff --git
a/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
b/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
index 1a34bc2dede4..eb39ddac6978 100644
---
a/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
+++
b/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
@@ -46,44 +46,31 @@ WHERE
column_name op { value | value_list } [ AND column_name op { value |
value_list } ...];
```
-<version since="dev">
-
Syntax 2：This syntax can only used on UNIQUE KEY model
```sql
+[cte]
DELETE FROM table_name
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ table_name: Specifies the table from which rows are removed.
+ column_name: column belong to table_name
+ op: Logical comparison operator, The optional types of op include: =, >, <,
>=, <=, !=, in, not in
+ value | value_list: value or value list used for logial comparison
-
-<version since="dev">
-
+ WHERE condition: Specifies a condition to use to select rows for removal
-</version>
-
-
#### Optional Parameters
++ cte: Common Table Expression, e.g. 'WITH a AS SELECT * FROM tbl'.
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
Specifies the partition or partitions to select rows for removal
-
-<version since="dev">
-
+ table_alias: alias of table
+ USING additional_tables: If you need to refer to additional tables in the
WHERE clause to help identify the rows to be removed, then specify those table
names in the USING clause. You can also use the USING clause to specify
subqueries that identify the rows to be removed.
-</version>
-
#### Note
1. Only conditions on the key column can be specified when using AGGREGATE
(UNIQUE) model.
@@ -115,8 +102,6 @@ DELETE FROM table_name
WHERE k1 >= 3 AND k2 = "abc";
````
-<version since="dev">
-
4. use the result of `t2` join `t3` to romve rows from `t1`,delete table only
support unique key model
```sql
@@ -172,7 +157,46 @@ DELETE FROM table_name
+----+----+----+--------+------------+
```
-</version>
+5. using cte
+
+ ```sql
+ create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+ ) unique key(o_orderkey)
+ distributed by hash(o_orderkey) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into orders values
+ (1, 34.1),
+ (2, 432.8);
+
+ create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+ ) unique key(l_linenumber)
+ distributed by hash(l_linenumber) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into lineitem values
+ (1, 1, 1.23),
+ (2, 1, 3.21),
+ (3, 2, 18.08),
+ (4, 2, 23.48);
+
+ with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+ )
+ delete from lineitem
+ using discount_orders
+ where lineitem.o_orderkey = discount_orders.o_orderkey;
+ ```
### Keywords
diff --git
a/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
b/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
index 050e59d393c9..b7c3fc5933fc 100644
---
a/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
+++
b/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
@@ -38,31 +38,13 @@ The UPDATE operation currently only supports updating the
Value column. The upda
#### Syntax
```sql
-UPDATE target_table [table_alias]
- SET assignment_list
- WHERE condition
-
-assignment_list:
- assignment [, assignment] ...
-
-assignment:
- col_name = value
-
-value:
- {expr | DEFAULT}
-```
-
-<version since="dev">
-
-```sql
+[cte]
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ target_table: The target table of the data to be updated. Can be of the form
'db_name.table_name'
@@ -71,13 +53,10 @@ UPDATE target_table [table_alias]
#### Optional Parameters
-<version since="dev">
-
++ cte: Common Table Expression, e.g. 'WITH a AS SELECT * FROM tbl'
+ table_alias: alias of table
+ FROM additional_tables: Specifies one or more tables to use for selecting
rows to update or for setting new values. Note that if you want use target
table here, you should give it a alias explicitly.
-</version>
-
#### Note
The current UPDATE statement only supports row updates on the UNIQUE KEY model.
@@ -98,8 +77,6 @@ UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;
UPDATE test SET v1 = v1+1 WHERE k1=1;
```
-<version since="dev">
-
3. use the result of `t2` join `t3` to update `t1`
```sql
@@ -157,7 +134,46 @@ the expect result is only update the row where id = 1 in
table t1
+----+----+----+--------+------------+
```
-</version>
+4. using cte
+
+```sql
+create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+) unique key(o_orderkey)
+distributed by hash(o_orderkey) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into orders values
+(1, 34.1),
+(2, 432.8);
+
+create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+) unique key(l_linenumber)
+distributed by hash(l_linenumber) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into lineitem values
+(1, 1, 1.23),
+(2, 1, 3.21),
+(3, 2, 18.08),
+(4, 2, 23.48);
+
+with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+)
+update lineitem set l_discount = l_discount*0.9
+from discount_orders
+where lineitem.o_orderkey = discount_orders.o_orderkey;
+```
### Keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
index a2c44445904d..8eb74274015b 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
@@ -46,44 +46,31 @@ WHERE
column_name op { value | value_list } [ AND column_name op { value |
value_list } ...];
```
-<version since="dev">
-
语法二：该语法只能在UNIQUE KEY模型表上使用
```sql
+[cte]
DELETE FROM table_name [table_alias]
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ table_name: 指定需要删除数据的表
+ column_name: 属于table_name的列
+ op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
+ value | value_list: 做逻辑比较的值或值列表
-
-<version since="dev">
-
+ WHERE condition: 指定一个用于选择删除行的条件
-</version>
-
-
#### Optional Parameters
++ cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
指定执行删除数据的分区名，如果表不存在此分区，则报错
-
-<version since="dev">
-
+ table_alias: 表的别名
+ USING additional_tables: 如果需要在WHERE语句中使用其他的表来帮助识别需要删除的行，则可以在USING中指定这些表或者查询。
-</version>
-
#### Note
1. 使用聚合类的表模型（AGGREGATE、UNIQUE）只能指定 key 列上的条件。
@@ -115,8 +102,6 @@ DELETE FROM table_name [table_alias]
WHERE k1 >= 3 AND k2 = "abc";
```
-<version since="dev">
-
4. 使用`t2`和`t3`表连接的结果，删除`t1`中的数据，删除的表只支持unique模型
```sql
@@ -172,7 +157,46 @@ DELETE FROM table_name [table_alias]
+----+----+----+--------+------------+
```
-</version>
+5. 使用 cte 关联删除
+
+ ```sql
+ create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+ ) unique key(o_orderkey)
+ distributed by hash(o_orderkey) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into orders values
+ (1, 34.1),
+ (2, 432.8);
+
+ create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+ ) unique key(l_linenumber)
+ distributed by hash(l_linenumber) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into lineitem values
+ (1, 1, 1.23),
+ (2, 1, 3.21),
+ (3, 2, 18.08),
+ (4, 2, 23.48);
+
+ with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+ )
+ delete from lineitem
+ using discount_orders
+ where lineitem.o_orderkey = discount_orders.o_orderkey;
+ ```
### Keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
index c898de7251e7..e7b87cd5a489 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
@@ -39,31 +39,13 @@ UPDATE操作目前只支持更新Value列，Key列的更新可参考[使用Flink
#### Syntax
```sql
-UPDATE target_table [table_alias]
- SET assignment_list
- WHERE condition
-
-assignment_list:
- assignment [, assignment] ...
-
-assignment:
- col_name = value
-
-value:
- {expr | DEFAULT}
-```
-
-<version since="dev">
-
-```sql
+[cte]
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ target_table: 待更新数据的目标表。可以是 'db_name.table_name' 形式
@@ -72,13 +54,10 @@ UPDATE target_table [table_alias]
#### Optional Parameters
-<version since="dev">
-
++ cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
+ table_alias: 表的别名
+ FROM additional_tables:
指定一个或多个表，用于选中更新的行，或者获取更新的值。注意，如需要在此列表中再次使用目标表，需要为其显式指定别名。
-</version>
-
#### Note
当前 UPDATE 语句仅支持在 UNIQUE KEY 模型上的行更新。
@@ -99,8 +78,6 @@ UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;
UPDATE test SET v1 = v1+1 WHERE k1=1;
```
-<version since="dev">
-
3. 使用`t2`和`t3`表连接的结果，更新`t1`
```sql
@@ -158,7 +135,46 @@ UPDATE t1
+----+----+----+--------+------------+
```
-</version>
+4. 使用 cte 更新表
+
+```sql
+create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+) unique key(o_orderkey)
+distributed by hash(o_orderkey) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into orders values
+(1, 34.1),
+(2, 432.8);
+
+create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+) unique key(l_linenumber)
+distributed by hash(l_linenumber) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into lineitem values
+(1, 1, 1.23),
+(2, 1, 3.21),
+(3, 2, 18.08),
+(4, 2, 23.48);
+
+with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+)
+update lineitem set l_discount = l_discount*0.9
+from discount_orders
+where lineitem.o_orderkey = discount_orders.o_orderkey;
+```
### Keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
index a2c44445904d..8eb74274015b 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
@@ -46,44 +46,31 @@ WHERE
column_name op { value | value_list } [ AND column_name op { value |
value_list } ...];
```
-<version since="dev">
-
语法二：该语法只能在UNIQUE KEY模型表上使用
```sql
+[cte]
DELETE FROM table_name [table_alias]
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ table_name: 指定需要删除数据的表
+ column_name: 属于table_name的列
+ op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
+ value | value_list: 做逻辑比较的值或值列表
-
-<version since="dev">
-
+ WHERE condition: 指定一个用于选择删除行的条件
-</version>
-
-
#### Optional Parameters
++ cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
指定执行删除数据的分区名，如果表不存在此分区，则报错
-
-<version since="dev">
-
+ table_alias: 表的别名
+ USING additional_tables: 如果需要在WHERE语句中使用其他的表来帮助识别需要删除的行，则可以在USING中指定这些表或者查询。
-</version>
-
#### Note
1. 使用聚合类的表模型（AGGREGATE、UNIQUE）只能指定 key 列上的条件。
@@ -115,8 +102,6 @@ DELETE FROM table_name [table_alias]
WHERE k1 >= 3 AND k2 = "abc";
```
-<version since="dev">
-
4. 使用`t2`和`t3`表连接的结果，删除`t1`中的数据，删除的表只支持unique模型
```sql
@@ -172,7 +157,46 @@ DELETE FROM table_name [table_alias]
+----+----+----+--------+------------+
```
-</version>
+5. 使用 cte 关联删除
+
+ ```sql
+ create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+ ) unique key(o_orderkey)
+ distributed by hash(o_orderkey) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into orders values
+ (1, 34.1),
+ (2, 432.8);
+
+ create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+ ) unique key(l_linenumber)
+ distributed by hash(l_linenumber) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into lineitem values
+ (1, 1, 1.23),
+ (2, 1, 3.21),
+ (3, 2, 18.08),
+ (4, 2, 23.48);
+
+ with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+ )
+ delete from lineitem
+ using discount_orders
+ where lineitem.o_orderkey = discount_orders.o_orderkey;
+ ```
### Keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
index c898de7251e7..e7b87cd5a489 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
@@ -39,31 +39,13 @@ UPDATE操作目前只支持更新Value列，Key列的更新可参考[使用Flink
#### Syntax
```sql
-UPDATE target_table [table_alias]
- SET assignment_list
- WHERE condition
-
-assignment_list:
- assignment [, assignment] ...
-
-assignment:
- col_name = value
-
-value:
- {expr | DEFAULT}
-```
-
-<version since="dev">
-
-```sql
+[cte]
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ target_table: 待更新数据的目标表。可以是 'db_name.table_name' 形式
@@ -72,13 +54,10 @@ UPDATE target_table [table_alias]
#### Optional Parameters
-<version since="dev">
-
++ cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
+ table_alias: 表的别名
+ FROM additional_tables:
指定一个或多个表，用于选中更新的行，或者获取更新的值。注意，如需要在此列表中再次使用目标表，需要为其显式指定别名。
-</version>
-
#### Note
当前 UPDATE 语句仅支持在 UNIQUE KEY 模型上的行更新。
@@ -99,8 +78,6 @@ UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;
UPDATE test SET v1 = v1+1 WHERE k1=1;
```
-<version since="dev">
-
3. 使用`t2`和`t3`表连接的结果，更新`t1`
```sql
@@ -158,7 +135,46 @@ UPDATE t1
+----+----+----+--------+------------+
```
-</version>
+4. 使用 cte 更新表
+
+```sql
+create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+) unique key(o_orderkey)
+distributed by hash(o_orderkey) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into orders values
+(1, 34.1),
+(2, 432.8);
+
+create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+) unique key(l_linenumber)
+distributed by hash(l_linenumber) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into lineitem values
+(1, 1, 1.23),
+(2, 1, 3.21),
+(3, 2, 18.08),
+(4, 2, 23.48);
+
+with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+)
+update lineitem set l_discount = l_discount*0.9
+from discount_orders
+where lineitem.o_orderkey = discount_orders.o_orderkey;
+```
### Keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
index 1a34bc2dede4..eb39ddac6978 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
@@ -46,44 +46,31 @@ WHERE
column_name op { value | value_list } [ AND column_name op { value |
value_list } ...];
```
-<version since="dev">
-
Syntax 2：This syntax can only used on UNIQUE KEY model
```sql
+[cte]
DELETE FROM table_name
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ table_name: Specifies the table from which rows are removed.
+ column_name: column belong to table_name
+ op: Logical comparison operator, The optional types of op include: =, >, <,
>=, <=, !=, in, not in
+ value | value_list: value or value list used for logial comparison
-
-<version since="dev">
-
+ WHERE condition: Specifies a condition to use to select rows for removal
-</version>
-
-
#### Optional Parameters
++ cte: Common Table Expression, e.g. 'WITH a AS SELECT * FROM tbl'.
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
Specifies the partition or partitions to select rows for removal
-
-<version since="dev">
-
+ table_alias: alias of table
+ USING additional_tables: If you need to refer to additional tables in the
WHERE clause to help identify the rows to be removed, then specify those table
names in the USING clause. You can also use the USING clause to specify
subqueries that identify the rows to be removed.
-</version>
-
#### Note
1. Only conditions on the key column can be specified when using AGGREGATE
(UNIQUE) model.
@@ -115,8 +102,6 @@ DELETE FROM table_name
WHERE k1 >= 3 AND k2 = "abc";
````
-<version since="dev">
-
4. use the result of `t2` join `t3` to romve rows from `t1`,delete table only
support unique key model
```sql
@@ -172,7 +157,46 @@ DELETE FROM table_name
+----+----+----+--------+------------+
```
-</version>
+5. using cte
+
+ ```sql
+ create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+ ) unique key(o_orderkey)
+ distributed by hash(o_orderkey) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into orders values
+ (1, 34.1),
+ (2, 432.8);
+
+ create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+ ) unique key(l_linenumber)
+ distributed by hash(l_linenumber) buckets 1
+ properties (
+ "replication_num" = "1"
+ );
+
+ insert into lineitem values
+ (1, 1, 1.23),
+ (2, 1, 3.21),
+ (3, 2, 18.08),
+ (4, 2, 23.48);
+
+ with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+ )
+ delete from lineitem
+ using discount_orders
+ where lineitem.o_orderkey = discount_orders.o_orderkey;
+ ```
### Keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
index 050e59d393c9..63c62a704b0d 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
@@ -38,31 +38,13 @@ The UPDATE operation currently only supports updating the
Value column. The upda
#### Syntax
```sql
-UPDATE target_table [table_alias]
- SET assignment_list
- WHERE condition
-
-assignment_list:
- assignment [, assignment] ...
-
-assignment:
- col_name = value
-
-value:
- {expr | DEFAULT}
-```
-
-<version since="dev">
-
-```sql
+[cte]
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
WHERE condition
```
-</version>
-
#### Required Parameters
+ target_table: The target table of the data to be updated. Can be of the form
'db_name.table_name'
@@ -71,13 +53,10 @@ UPDATE target_table [table_alias]
#### Optional Parameters
-<version since="dev">
-
++ cte: Common Table Expression, eg 'WITH a AS SELECT * FROM tbl'
+ table_alias: alias of table
+ FROM additional_tables: Specifies one or more tables to use for selecting
rows to update or for setting new values. Note that if you want use target
table here, you should give it a alias explicitly.
-</version>
-
#### Note
The current UPDATE statement only supports row updates on the UNIQUE KEY model.
@@ -98,8 +77,6 @@ UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;
UPDATE test SET v1 = v1+1 WHERE k1=1;
```
-<version since="dev">
-
3. use the result of `t2` join `t3` to update `t1`
```sql
@@ -157,7 +134,46 @@ the expect result is only update the row where id = 1 in
table t1
+----+----+----+--------+------------+
```
-</version>
+4. using cte
+
+```sql
+create table orders(
+ o_orderkey bigint,
+ o_totalprice decimal(15, 2)
+) unique key(o_orderkey)
+distributed by hash(o_orderkey) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into orders values
+(1, 34.1),
+(2, 432.8);
+
+create table lineitem(
+ l_linenumber int,
+ o_orderkey bigint,
+ l_discount decimal(15, 2)
+) unique key(l_linenumber)
+distributed by hash(l_linenumber) buckets 1
+properties (
+ "replication_num" = "1"
+);
+
+insert into lineitem values
+(1, 1, 1.23),
+(2, 1, 3.21),
+(3, 2, 18.08),
+(4, 2, 23.48);
+
+with discount_orders as (
+ select * from orders
+ where o_totalprice > 100
+)
+update lineitem set l_discount = l_discount*0.9
+from discount_orders
+where lineitem.o_orderkey = discount_orders.o_orderkey;
+```
### Keywords
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
