This is an automated email from the ASF dual-hosted git repository. morrysnow pushed a commit to branch update_delete_update_doc in repository https://gitbox.apache.org/repos/asf/doris-website.git
commit d47bd0c2ec667022da25456952efb6d39da76fa4 Author: morrySnow <[email protected]> AuthorDate: Thu Mar 26 14:29:27 2026 +0800 [docs] Document ORDER BY and LIMIT support in UPDATE and DELETE statements Add documentation for the ORDER BY and LIMIT clauses in UPDATE and DELETE statements, as introduced by apache/doris#61681. Changes across dev (current) and 4.x versions, both English and Chinese: - Updated syntax blocks to include ORDER BY and LIMIT clauses - Added parameter descriptions for ORDER BY and LIMIT - Added usage examples with ORDER BY, LIMIT, and offset - Added 'since 4.1.0' version tags in 4.x versioned docs Co-authored-by: Copilot <[email protected]> --- .../sql-statements/data-modification/DML/DELETE.md | 16 ++++++++++++++++ .../sql-statements/data-modification/DML/UPDATE.md | 16 ++++++++++++++++ .../sql-statements/data-modification/DML/DELETE.md | 16 ++++++++++++++++ .../sql-statements/data-modification/DML/UPDATE.md | 16 ++++++++++++++++ .../sql-statements/data-modification/DML/DELETE.md | 20 ++++++++++++++++++++ .../sql-statements/data-modification/DML/UPDATE.md | 20 ++++++++++++++++++++ .../sql-statements/data-modification/DML/DELETE.md | 20 ++++++++++++++++++++ .../sql-statements/data-modification/DML/UPDATE.md | 20 ++++++++++++++++++++ 8 files changed, 144 insertions(+) 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..2ca7455f5ed 100644 --- a/docs/sql-manual/sql-statements/data-modification/DML/DELETE.md +++ b/docs/sql-manual/sql-statements/data-modification/DML/DELETE.md @@ -30,6 +30,8 @@ DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])] [USING additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -46,6 +48,8 @@ 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. ++ ORDER BY column: Specifies the order in which rows are deleted. 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. #### Note @@ -179,6 +183,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..8f820a1f8c5 100644 --- a/docs/sql-manual/sql-statements/data-modification/DML/UPDATE.md +++ b/docs/sql-manual/sql-statements/data-modification/DML/UPDATE.md @@ -19,6 +19,8 @@ UPDATE target_table [table_alias] SET assignment_list [ FROM additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -32,6 +34,8 @@ UPDATE target_table [table_alias] + 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. ++ ORDER BY column: Specifies the order in which rows are updated. 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. #### Note @@ -151,6 +155,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..b56aa28687c 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 @@ -30,6 +30,8 @@ DELETE FROM table_name [table_alias] [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])] [USING additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -46,6 +48,8 @@ DELETE FROM table_name [table_alias] + PARTITION partition_name | PARTITIONS (partition_name [, partition_name]): 指定执行删除数据的分区名，如果表不存在此分区，则报错 + table_alias: 表的别名 + USING additional_tables: 如果需要在 WHERE 语句中使用其他的表来帮助识别需要删除的行，则可以在 USING 中指定这些表或者查询。 ++ ORDER BY column: 指定删除行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被删除。 ++ LIMIT [offset,] count: 限制删除的行数。与 ORDER BY 一起使用时，排序后删除前 `count` 行。如果指定了 `offset`，则跳过排序后的前 `offset` 行再进行删除。 #### Note @@ -174,6 +178,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..71189234c31 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 @@ -20,6 +20,8 @@ UPDATE target_table [table_alias] SET assignment_list [ FROM additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -33,6 +35,8 @@ UPDATE target_table [table_alias] + cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式 + table_alias: 表的别名 + FROM additional_tables: 指定一个或多个表，用于选中更新的行，或者获取更新的值。注意，如需要在此列表中再次使用目标表，需要为其显式指定别名。 ++ ORDER BY column: 指定更新行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被更新。 ++ LIMIT [offset,] count: 限制更新的行数。与 ORDER BY 一起使用时，排序后更新前 `count` 行。如果指定了 `offset`，则跳过排序后的前 `offset` 行再进行更新。 #### Note @@ -152,6 +156,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..7403e4793c4 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 @@ -30,6 +30,8 @@ DELETE FROM table_name [table_alias] [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])] [USING additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -46,6 +48,12 @@ DELETE FROM table_name [table_alias] + PARTITION partition_name | PARTITIONS (partition_name [, partition_name]): 指定执行删除数据的分区名，如果表不存在此分区，则报错 + table_alias: 表的别名 + USING additional_tables: 如果需要在 WHERE 语句中使用其他的表来帮助识别需要删除的行，则可以在 USING 中指定这些表或者查询。 ++ ORDER BY column: 指定删除行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被删除。 ++ LIMIT [offset,] count: 限制删除的行数。与 ORDER BY 一起使用时，排序后删除前 `count` 行。如果指定了 `offset`，则跳过排序后的前 `offset` 行再进行删除。 + +:::tip +DELETE 语句中的 ORDER BY 和 LIMIT 自 4.1.0 版本起支持。 +::: #### Note @@ -174,6 +182,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..9d64b9d3b82 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 @@ -20,6 +20,8 @@ UPDATE target_table [table_alias] SET assignment_list [ FROM additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -33,6 +35,12 @@ UPDATE target_table [table_alias] + cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式 + table_alias: 表的别名 + FROM additional_tables: 指定一个或多个表，用于选中更新的行，或者获取更新的值。注意，如需要在此列表中再次使用目标表，需要为其显式指定别名。 ++ ORDER BY column: 指定更新行的排序方式。通常与 LIMIT 一起使用，以控制哪些行会被更新。 ++ LIMIT [offset,] count: 限制更新的行数。与 ORDER BY 一起使用时，排序后更新前 `count` 行。如果指定了 `offset`，则跳过排序后的前 `offset` 行再进行更新。 + +:::tip +UPDATE 语句中的 ORDER BY 和 LIMIT 自 4.1.0 版本起支持。 +::: #### Note @@ -152,6 +160,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..2c74cb24fbc 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 @@ -30,6 +30,8 @@ DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])] [USING additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -46,6 +48,12 @@ 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. ++ ORDER BY column: Specifies the order in which rows are deleted. 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. + +:::tip +ORDER BY and LIMIT in DELETE statements are supported since version 4.1.0. +::: #### Note @@ -179,6 +187,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..fdf1056c496 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 @@ -19,6 +19,8 @@ UPDATE target_table [table_alias] SET assignment_list [ FROM additional_tables] WHERE condition + [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]] + [LIMIT [offset,] count] ``` #### Required Parameters @@ -32,6 +34,12 @@ UPDATE target_table [table_alias] + 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. ++ ORDER BY column: Specifies the order in which rows are updated. 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. + +:::tip +ORDER BY and LIMIT in UPDATE statements are supported since version 4.1.0. +::: #### Note @@ -151,6 +159,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]
