This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 c02938081b4 [docs] Document ORDER BY and LIMIT support in UPDATE and
DELETE statements (#3495)
c02938081b4 is described below
commit c02938081b4261aaa151da90b25df3bd1d204d61
Author: morrySnow <[email protected]>
AuthorDate: Thu Mar 26 15:59:41 2026 +0800
[docs] Document ORDER BY and LIMIT support in UPDATE and DELETE statements
(#3495)
## Summary
Add documentation for the ORDER BY and LIMIT clauses in UPDATE and
DELETE statements, as introduced by
https://github.com/apache/doris/pull/61681.
## Changes
Updated 8 documentation files across dev (current) and 4.x versions,
both English and Chinese:
- **Syntax**: Added `[ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS
LAST] [, ...]]` and `[LIMIT [offset,] count]` clauses to UPDATE and
DELETE syntax blocks
- **Parameters**: Added descriptions for ORDER BY and LIMIT optional
parameters
- **Examples**: Added usage examples demonstrating ORDER BY + LIMIT and
ORDER BY + LIMIT with offset
- **Version tags**: Added `:::tip Supported since version 4.1.0. :::` in
4.x versioned docs only
### Files Modified
| Version | Language | File |
|---------|----------|------|
| dev | EN | `docs/.../DML/UPDATE.md` |
| dev | EN | `docs/.../DML/DELETE.md` |
| 4.x | EN | `versioned_docs/version-4.x/.../DML/UPDATE.md` |
| 4.x | EN | `versioned_docs/version-4.x/.../DML/DELETE.md` |
| dev | ZH | `i18n/zh-CN/.../current/.../DML/UPDATE.md` |
| dev | ZH | `i18n/zh-CN/.../current/.../DML/DELETE.md` |
| 4.x | ZH | `i18n/zh-CN/.../version-4.x/.../DML/UPDATE.md` |
| 4.x | ZH | `i18n/zh-CN/.../version-4.x/.../DML/DELETE.md` |
## Related PR
- https://github.com/apache/doris/pull/61681
---------
Co-authored-by: Copilot <[email protected]>
---
.../sql-statements/data-modification/DML/DELETE.md | 21 +++++++++++++++--
.../sql-statements/data-modification/DML/UPDATE.md | 22 ++++++++++++++++--
.../sql-statements/data-modification/DML/DELETE.md | 21 +++++++++++++++--
.../sql-statements/data-modification/DML/UPDATE.md | 22 ++++++++++++++++--
.../sql-statements/data-modification/DML/DELETE.md | 25 +++++++++++++++++++--
.../sql-statements/data-modification/DML/UPDATE.md | 26 ++++++++++++++++++++--
.../sql-statements/data-modification/DML/DELETE.md | 25 +++++++++++++++++++--
.../sql-statements/data-modification/DML/UPDATE.md | 26 ++++++++++++++++++++--
8 files changed, 172 insertions(+), 16 deletions(-)
diff --git a/docs/sql-manual/sql-statements/data-modification/DML/DELETE.md
b/docs/sql-manual/sql-statements/data-modification/DML/DELETE.md
index dc2c22396fb..00e87b0a83c 100644
--- a/docs/sql-manual/sql-statements/data-modification/DML/DELETE.md
+++ b/docs/sql-manual/sql-statements/data-modification/DML/DELETE.md
@@ -29,7 +29,9 @@ Syntax 2: This syntax can only used on UNIQUE KEY model
DELETE FROM table_name
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
@@ -38,7 +40,6 @@ DELETE FROM table_name
+ 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
-+ WHERE condition: Specifies a condition to use to select rows for removal
#### Optional Parameters
@@ -46,6 +47,9 @@ DELETE FROM table_name
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
Specifies the partition or partitions to select rows for removal
+ 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.
++ WHERE condition: Specifies a condition to use to select rows for removal.
Required for Syntax 1. Optional for Syntax 2.
++ ORDER BY column: Specifies the order in which rows are deleted. Typically
used together with LIMIT to control which rows are affected.
++ LIMIT [offset,] count: Limits the number of rows to be deleted. When used
with ORDER BY, deletes the first `count` rows after sorting. If `offset` is
specified, skips the first `offset` rows before deleting. If used without ORDER
BY, the set of affected rows is non-deterministic.
#### Note
@@ -59,6 +63,7 @@ This feature is supported since the Apache Doris 1.2 version
:::
5. This statement may reduce query efficiency for a period of time after
execution. The degree of impact depends on the number of delete conditions
specified in the statement. The more conditions you specify, the greater the
impact.
+6. When neither `WHERE` nor `LIMIT` is specified in Syntax 2, all rows in the
table will be deleted. Always verify the intended scope before omitting the
`WHERE` clause.
## Example
@@ -179,6 +184,18 @@ This feature is supported since the Apache Doris 1.2
version
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+6. Delete with ORDER BY and LIMIT — delete the first 3 rows ordered by k1 in
ascending order
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 3;
+ ```
+
+7. Delete with ORDER BY, LIMIT and offset — skip the first 10 rows and delete
the next 5 rows ordered by k1
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 10, 5;
+ ```
+
## Keywords
DELETE
diff --git a/docs/sql-manual/sql-statements/data-modification/DML/UPDATE.md
b/docs/sql-manual/sql-statements/data-modification/DML/UPDATE.md
index a900a19db72..5d6634c1730 100644
--- a/docs/sql-manual/sql-statements/data-modification/DML/UPDATE.md
+++ b/docs/sql-manual/sql-statements/data-modification/DML/UPDATE.md
@@ -18,25 +18,31 @@ The UPDATE operation currently only supports updating the
Value column. The upda
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
+ target_table: The target table of the data to be updated. Can be of the form
'db_name.table_name'
+ assignment_list: The target column to be updated, in the format 'col_name =
value, col_name = value'
-+ where condition: the condition that is expected to be updated, an expression
that returns true or false can be
#### Optional Parameters
+ 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.
++ WHERE condition: The condition that is expected to be updated, an expression
that returns true or false.
++ ORDER BY column: Specifies the order in which rows are updated. Typically
used together with LIMIT to control which rows are affected.
++ LIMIT [offset,] count: Limits the number of rows to be updated. When used
with ORDER BY, updates the first `count` rows after sorting. If `offset` is
specified, skips the first `offset` rows before updating. If used without ORDER
BY, the set of affected rows is non-deterministic.
#### Note
The current UPDATE statement only supports row updates on the UNIQUE KEY model.
+When neither `WHERE` nor `LIMIT` is specified, all rows in the table will be
updated. Always verify the intended scope before omitting the `WHERE` clause.
+
## Example
The `test` table is a unique model table, which contains four columns: k1, k2,
v1, v2. Where k1, k2 are keys, v1, v2 are values, and the aggregation method is
Replace.
@@ -151,6 +157,18 @@ from discount_orders
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+5. Update with ORDER BY and LIMIT — update the v1 column to 0 for the first 3
rows with the largest v1 values where k1 > 0
+
+```sql
+UPDATE test SET v1 = 0 WHERE k1 > 0 ORDER BY v1 DESC LIMIT 3;
+```
+
+6. Update with ORDER BY, LIMIT and offset — skip the first 10 rows and update
the next 5 rows ordered by k1
+
+```sql
+UPDATE test SET v1 = 100 ORDER BY k1 ASC LIMIT 10, 5;
+```
+
## Keywords
UPDATE
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/DELETE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/DELETE.md
index 9001d318d37..aa3c6df6b2a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/DELETE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/DELETE.md
@@ -29,7 +29,9 @@ column_name op { value | value_list } [ AND column_name op {
value | value_list
DELETE FROM table_name [table_alias]
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
@@ -38,7 +40,6 @@ DELETE FROM table_name [table_alias]
+ column_name: 属于 table_name 的列
+ op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
+ value | value_list: 做逻辑比较的值或值列表
-+ WHERE condition: 指定一个用于选择删除行的条件
#### Optional Parameters
@@ -46,6 +47,9 @@ DELETE FROM table_name [table_alias]
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
指定执行删除数据的分区名，如果表不存在此分区，则报错
+ table_alias: 表的别名
+ USING additional_tables: 如果需要在 WHERE 语句中使用其他的表来帮助识别需要删除的行，则可以在 USING
中指定这些表或者查询。
++ WHERE condition: 指定一个用于选择删除行的条件。语法一中为必填项，语法二中为可选项。
++ ORDER BY column: 指定删除行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被删除。
++ LIMIT [offset,] count: 限制删除的行数。与 ORDER BY 一起使用时，排序后删除前 `count` 行。如果指定了
`offset`，则跳过排序后的前 `offset` 行再进行删除。如果不配合 ORDER BY 使用，受影响的行是不确定的。
#### Note
@@ -54,6 +58,7 @@ DELETE FROM table_name [table_alias]
3. 语法一中，条件之间只能是“与”的关系。若希望达成“或”的关系，需要将条件分写在两个 DELETE 语句中。
4. 语法一中，如果为分区表，需要指定分区，如果不指定，doris 会从条件中推断出分区。两种情况下，doris 无法从条件中推断出分区：1)
条件中不包含分区列；2) 分区列的 op 为 not in。如果分区表不是 Unique
表，当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 delete
会应用到所有分区。
5. 该语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多，影响越大。
+6. 在语法二中，如果既未指定 `WHERE` 也未指定 `LIMIT`，则会删除表中的所有行。请在省略 `WHERE` 子句前务必确认操作范围。
## 示例
@@ -174,6 +179,18 @@ DELETE FROM table_name [table_alias]
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+6. 使用 ORDER BY 和 LIMIT 删除数据——按照 k1 列升序排列，删除前 3 行
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 3;
+ ```
+
+7. 使用 ORDER BY、LIMIT 和 offset 删除数据——按照 k1 列升序排列，跳过前 10 行，删除接下来的 5 行
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 10, 5;
+ ```
+
## 关键词
DELETE
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/UPDATE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/UPDATE.md
index cf6ec4496a5..9670d1a8315 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/UPDATE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/data-modification/DML/UPDATE.md
@@ -19,25 +19,31 @@ UPDATE 操作目前只支持更新 Value 列，Key 列的更新可参考[使用
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
+ target_table: 待更新数据的目标表。可以是 'db_name.table_name' 形式
+ assignment_list: 待更新的目标列，形如 'col_name = value, col_name = value' 格式
-+ WHERE condition: 期望更新的条件，一个返回 true 或者 false 的表达式即可
#### Optional Parameters
+ cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
+ table_alias: 表的别名
+ FROM additional_tables:
指定一个或多个表，用于选中更新的行，或者获取更新的值。注意，如需要在此列表中再次使用目标表，需要为其显式指定别名。
++ WHERE condition: 期望更新的条件，一个返回 true 或者 false 的表达式即可。
++ ORDER BY column: 指定更新行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被更新。
++ LIMIT [offset,] count: 限制更新的行数。与 ORDER BY 一起使用时，排序后更新前 `count` 行。如果指定了
`offset`，则跳过排序后的前 `offset` 行再进行更新。如果不配合 ORDER BY 使用，受影响的行是不确定的。
#### Note
当前 UPDATE 语句仅支持在 UNIQUE KEY 模型上的行更新。
+如果既未指定 `WHERE` 也未指定 `LIMIT`，则会更新表中的所有行。请在省略 `WHERE` 子句前务必确认操作范围。
+
## 示例
`test` 表是一个 unique 模型的表，包含：k1, k2, v1, v2 四个列。其中 k1, k2 是 key，v1, v2 是
value，聚合方式是 Replace。
@@ -152,6 +158,18 @@ from discount_orders
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+5. 使用 ORDER BY 和 LIMIT 更新数据——按照 v1 列降序排列，更新满足 k1 > 0 条件的前 3 行的 v1 列为 0
+
+```sql
+UPDATE test SET v1 = 0 WHERE k1 > 0 ORDER BY v1 DESC LIMIT 3;
+```
+
+6. 使用 ORDER BY、LIMIT 和 offset 更新数据——按照 k1 列升序排列，跳过前 10 行，更新接下来的 5 行
+
+```sql
+UPDATE test SET v1 = 100 ORDER BY k1 ASC LIMIT 10, 5;
+```
+
## 关键词
UPDATE
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
index 9001d318d37..bc2c17c1002 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
@@ -29,7 +29,9 @@ column_name op { value | value_list } [ AND column_name op {
value | value_list
DELETE FROM table_name [table_alias]
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
@@ -38,7 +40,6 @@ DELETE FROM table_name [table_alias]
+ column_name: 属于 table_name 的列
+ op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
+ value | value_list: 做逻辑比较的值或值列表
-+ WHERE condition: 指定一个用于选择删除行的条件
#### Optional Parameters
@@ -46,6 +47,13 @@ DELETE FROM table_name [table_alias]
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
指定执行删除数据的分区名，如果表不存在此分区，则报错
+ table_alias: 表的别名
+ USING additional_tables: 如果需要在 WHERE 语句中使用其他的表来帮助识别需要删除的行，则可以在 USING
中指定这些表或者查询。
++ WHERE condition: 指定一个用于选择删除行的条件。语法一中为必填项，语法二中为可选项。
++ ORDER BY column: 指定删除行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被删除。
++ LIMIT [offset,] count: 限制删除的行数。与 ORDER BY 一起使用时，排序后删除前 `count` 行。如果指定了
`offset`，则跳过排序后的前 `offset` 行再进行删除。如果不配合 ORDER BY 使用，受影响的行是不确定的。
+
+:::tip
+DELETE 语句中的 ORDER BY 和 LIMIT 自 4.1.0 版本起支持。
+:::
#### Note
@@ -54,6 +62,7 @@ DELETE FROM table_name [table_alias]
3. 语法一中，条件之间只能是“与”的关系。若希望达成“或”的关系，需要将条件分写在两个 DELETE 语句中。
4. 语法一中，如果为分区表，需要指定分区，如果不指定，doris 会从条件中推断出分区。两种情况下，doris 无法从条件中推断出分区：1)
条件中不包含分区列；2) 分区列的 op 为 not in。如果分区表不是 Unique
表，当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 delete
会应用到所有分区。
5. 该语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多，影响越大。
+6. 在语法二中，如果既未指定 `WHERE` 也未指定 `LIMIT`，则会删除表中的所有行。请在省略 `WHERE` 子句前务必确认操作范围。
## 示例
@@ -174,6 +183,18 @@ DELETE FROM table_name [table_alias]
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+6. 使用 ORDER BY 和 LIMIT 删除数据——按照 k1 列升序排列，删除前 3 行
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 3;
+ ```
+
+7. 使用 ORDER BY、LIMIT 和 offset 删除数据——按照 k1 列升序排列，跳过前 10 行，删除接下来的 5 行
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 10, 5;
+ ```
+
## 关键词
DELETE
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
index cf6ec4496a5..2c4432c5697 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
@@ -19,25 +19,35 @@ UPDATE 操作目前只支持更新 Value 列，Key 列的更新可参考[使用
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
+ target_table: 待更新数据的目标表。可以是 'db_name.table_name' 形式
+ assignment_list: 待更新的目标列，形如 'col_name = value, col_name = value' 格式
-+ WHERE condition: 期望更新的条件，一个返回 true 或者 false 的表达式即可
#### Optional Parameters
+ cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
+ table_alias: 表的别名
+ FROM additional_tables:
指定一个或多个表，用于选中更新的行，或者获取更新的值。注意，如需要在此列表中再次使用目标表，需要为其显式指定别名。
++ WHERE condition: 期望更新的条件，一个返回 true 或者 false 的表达式即可。
++ ORDER BY column: 指定更新行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被更新。
++ LIMIT [offset,] count: 限制更新的行数。与 ORDER BY 一起使用时，排序后更新前 `count` 行。如果指定了
`offset`，则跳过排序后的前 `offset` 行再进行更新。如果不配合 ORDER BY 使用，受影响的行是不确定的。
+
+:::tip
+UPDATE 语句中的 ORDER BY 和 LIMIT 自 4.1.0 版本起支持。
+:::
#### Note
当前 UPDATE 语句仅支持在 UNIQUE KEY 模型上的行更新。
+如果既未指定 `WHERE` 也未指定 `LIMIT`，则会更新表中的所有行。请在省略 `WHERE` 子句前务必确认操作范围。
+
## 示例
`test` 表是一个 unique 模型的表，包含：k1, k2, v1, v2 四个列。其中 k1, k2 是 key，v1, v2 是
value，聚合方式是 Replace。
@@ -152,6 +162,18 @@ from discount_orders
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+5. 使用 ORDER BY 和 LIMIT 更新数据——按照 v1 列降序排列，更新满足 k1 > 0 条件的前 3 行的 v1 列为 0
+
+```sql
+UPDATE test SET v1 = 0 WHERE k1 > 0 ORDER BY v1 DESC LIMIT 3;
+```
+
+6. 使用 ORDER BY、LIMIT 和 offset 更新数据——按照 k1 列升序排列，跳过前 10 行，更新接下来的 5 行
+
+```sql
+UPDATE test SET v1 = 100 ORDER BY k1 ASC LIMIT 10, 5;
+```
+
## 关键词
UPDATE
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
b/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
index dc2c22396fb..a4a362d2be3 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/DELETE.md
@@ -29,7 +29,9 @@ Syntax 2: This syntax can only used on UNIQUE KEY model
DELETE FROM table_name
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
@@ -38,7 +40,6 @@ DELETE FROM table_name
+ 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
-+ WHERE condition: Specifies a condition to use to select rows for removal
#### Optional Parameters
@@ -46,6 +47,13 @@ DELETE FROM table_name
+ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
Specifies the partition or partitions to select rows for removal
+ 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.
++ WHERE condition: Specifies a condition to use to select rows for removal.
Required for Syntax 1. Optional for Syntax 2.
++ ORDER BY column: Specifies the order in which rows are deleted. Typically
used together with LIMIT to control which rows are affected.
++ LIMIT [offset,] count: Limits the number of rows to be deleted. When used
with ORDER BY, deletes the first `count` rows after sorting. If `offset` is
specified, skips the first `offset` rows before deleting. If used without ORDER
BY, the set of affected rows is non-deterministic.
+
+:::tip
+ORDER BY and LIMIT in DELETE statements are supported since version 4.1.0.
+:::
#### Note
@@ -59,6 +67,7 @@ This feature is supported since the Apache Doris 1.2 version
:::
5. This statement may reduce query efficiency for a period of time after
execution. The degree of impact depends on the number of delete conditions
specified in the statement. The more conditions you specify, the greater the
impact.
+6. When neither `WHERE` nor `LIMIT` is specified in Syntax 2, all rows in the
table will be deleted. Always verify the intended scope before omitting the
`WHERE` clause.
## Example
@@ -179,6 +188,18 @@ This feature is supported since the Apache Doris 1.2
version
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+6. Delete with ORDER BY and LIMIT — delete the first 3 rows ordered by k1 in
ascending order
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 3;
+ ```
+
+7. Delete with ORDER BY, LIMIT and offset — skip the first 10 rows and delete
the next 5 rows ordered by k1
+
+ ```sql
+ DELETE FROM my_table ORDER BY k1 ASC LIMIT 10, 5;
+ ```
+
## Keywords
DELETE
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
b/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
index a900a19db72..905a5235317 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-statements/data-modification/DML/UPDATE.md
@@ -18,25 +18,35 @@ The UPDATE operation currently only supports updating the
Value column. The upda
UPDATE target_table [table_alias]
SET assignment_list
[ FROM additional_tables]
- WHERE condition
+ [WHERE condition]
+ [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
+ [LIMIT [offset,] count]
```
#### Required Parameters
+ target_table: The target table of the data to be updated. Can be of the form
'db_name.table_name'
+ assignment_list: The target column to be updated, in the format 'col_name =
value, col_name = value'
-+ where condition: the condition that is expected to be updated, an expression
that returns true or false can be
#### Optional Parameters
+ 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.
++ WHERE condition: The condition that is expected to be updated, an expression
that returns true or false.
++ ORDER BY column: Specifies the order in which rows are updated. Typically
used together with LIMIT to control which rows are affected.
++ LIMIT [offset,] count: Limits the number of rows to be updated. When used
with ORDER BY, updates the first `count` rows after sorting. If `offset` is
specified, skips the first `offset` rows before updating. If used without ORDER
BY, the set of affected rows is non-deterministic.
+
+:::tip
+ORDER BY and LIMIT in UPDATE statements are supported since version 4.1.0.
+:::
#### Note
The current UPDATE statement only supports row updates on the UNIQUE KEY model.
+When neither `WHERE` nor `LIMIT` is specified, all rows in the table will be
updated. Always verify the intended scope before omitting the `WHERE` clause.
+
## Example
The `test` table is a unique model table, which contains four columns: k1, k2,
v1, v2. Where k1, k2 are keys, v1, v2 are values, and the aggregation method is
Replace.
@@ -151,6 +161,18 @@ from discount_orders
where lineitem.o_orderkey = discount_orders.o_orderkey;
```
+5. Update with ORDER BY and LIMIT — update the v1 column to 0 for the first 3
rows with the largest v1 values where k1 > 0
+
+```sql
+UPDATE test SET v1 = 0 WHERE k1 > 0 ORDER BY v1 DESC LIMIT 3;
+```
+
+6. Update with ORDER BY, LIMIT and offset — skip the first 10 rows and update
the next 5 rows ordered by k1
+
+```sql
+UPDATE test SET v1 = 100 ORDER BY k1 ASC LIMIT 10, 5;
+```
+
## Keywords
UPDATE
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
