This is an automated email from the ASF dual-hosted git repository.
zhangchen 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 60ce38bd29 Address comment on unique update.md and translate en doc by
LLM (#1719)
60ce38bd29 is described below
commit 60ce38bd29df9002c8d4886630a24d79c9f0adb4
Author: zhannngchen <[email protected]>
AuthorDate: Tue Jan 14 14:08:25 2025 +0800
Address comment on unique update.md and translate en doc by LLM (#1719)
## Versions
- [x] dev
- [x] 3.0
- [x] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [x] Checked by AI
- [x] Test Cases Built
---
docs/data-operate/update/unique-update.md | 52 +++++++++++-----------
.../current/data-operate/update/unique-update.md | 8 ++--
.../data-operate/update/unique-update.md | 9 ++--
.../data-operate/update/unique-update.md | 8 ++--
.../data-operate/update/unique-update.md | 52 +++++++++++-----------
.../data-operate/update/unique-update.md | 52 +++++++++++-----------
6 files changed, 90 insertions(+), 91 deletions(-)
diff --git a/docs/data-operate/update/unique-update.md
b/docs/data-operate/update/unique-update.md
index aae397dfd4..58386e2679 100644
--- a/docs/data-operate/update/unique-update.md
+++ b/docs/data-operate/update/unique-update.md
@@ -24,33 +24,33 @@ specific language governing permissions and limitations
under the License.
-->
-The main topic is how to use the UPDATE command to update data in Doris. The
UPDATE command can only be executed on tables in the Unique data model.
+This document explains how to use the `UPDATE` command to modify data in
Doris. The `UPDATE` command is only applicable to tables with a Unique data
model.
-## Use Cases
+## Applicable Scenarios
-- Modify the values of rows that meet certain conditions.
+- Small-scale data updates: Ideal for scenarios where a small amount of data
needs to be corrected, such as fixing erroneous fields in certain records or
updating the status of specific fields (e.g., order status updates).
-- Suitable for updating a small amount of data that is not frequently updated.
+- ETL batch processing of certain fields: Suitable for large-scale updates of
a specific field, commonly seen in ETL processing scenarios. Note: Large-scale
data updates should be infrequent.
-## Basic Principles
+## How It Works
-By utilizing the filtering logic of the query engine's WHERE clause, the
UPDATE command selects the rows that need to be updated from the target table.
Then, using the built-in logic of the Value column in the Unique model, the old
data is replaced with the new data, and the updated rows are reinserted into
the table, thus achieving row-level updates.
+The query engine uses its own filtering logic to identify the rows that need
to be updated. Then, using the Unique model's Value column logic, it replaces
old data with new data. The rows to be updated are modified and reinserted into
the table to achieve row-level updates.
### Synchronization
-The UPDATE syntax in Doris is synchronous, meaning that when an UPDATE
statement is executed successfully, the update operation is completed, and the
data is immediately visible.
+The `UPDATE` syntax in Doris is synchronous, meaning that once the `UPDATE`
statement is successfully executed, the update operation is completed and the
data is immediately visible.
### Performance
-The performance of the UPDATE statement depends on the number of rows to be
updated and the efficiency of the condition retrieval.
+The performance of the `UPDATE` statement is closely related to the number of
rows to be updated and the efficiency of the query conditions.
-- Number of rows to be updated: The more rows that need to be updated, the
slower the UPDATE statement will be. The UPDATE command is suitable for
scenarios where occasional updates are required, such as modifying values for
individual rows. It is not suitable for bulk data modifications.
+- Number of rows to be updated: The more rows that need updating, the slower
the `UPDATE` statement will be. For small-scale updates, Doris supports a
frequency similar to `INSERT INTO` statements. For large-scale updates, due to
the long execution time, it is only suitable for infrequent calls.
-- Efficiency of condition retrieval: The UPDATE operation reads the rows that
satisfy the condition first. Therefore, if the condition retrieval is
efficient, the UPDATE operation will be faster. It is recommended to have the
condition column indexed or utilize partitioning and bucketing pruning to
quickly locate the rows to be updated, thus improving the update efficiency. It
is strongly advised not to include the value column in the condition column.
+- Efficiency of query conditions: The `UPDATE` implementation first reads the
rows that meet the query conditions. Therefore, if the query conditions are
efficient, the `UPDATE` speed will be fast. It is best if the condition columns
can hit the index or partition bucket pruning, so Doris does not need to scan
the entire table and can quickly locate the rows that need updating, thereby
improving update efficiency. It is strongly recommended not to include value
columns in the condition columns.
-## Example
+## Usage Example
-Assuming in a financial risk control scenario, there is a transaction details
table with the following structure:
+Assume there is a transaction details table with the following structure in a
financial risk control scenario:
```sql
CREATE TABLE transaction_details (
@@ -70,7 +70,7 @@ UNIQUE KEY(transaction_id)
DISTRIBUTED BY HASH(transaction_id) BUCKETS 16
PROPERTIES (
"replication_num" = "3", -- Number of replicas, default is 3
- "enable_unique_key_merge_on_write" = "true" -- Enable MOW mode, support
merge update
+ "enable_unique_key_merge_on_write" = "true" -- Enable MOW mode, support
merge updates
);
```
@@ -88,20 +88,20 @@ The following transaction data exists:
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
```
-Update the risk level of all transactions on a daily basis according to the
following risk control rules:
-1. If there is a dispute history, the risk is high.
-2. If in a high-risk region, the risk is high.
-3. If the transaction amount is abnormal (more than 5 times the daily
average), the risk is high.
+Update the risk level of all daily transaction records according to the
following risk control rules:
+1. Transactions with a dispute history have a risk level of high.
+2. Transactions in high-risk regions have a risk level of high.
+3. Transactions with abnormal amounts (exceeding 5 times the daily average)
have a risk level of high.
4. Frequent transactions in the last 7 days:
- a. If the number of transactions > 50, the risk is high.
- b. If the number of transactions is between 20 and 50, the risk is medium.
-5. Transactions during non-working hours (2 AM to 4 AM), the risk is medium.
-6. The default risk is low.
+ a. Transactions > 50 times have a risk level of high.
+ b. Transactions between 20 and 50 times have a risk level of medium.
+5. Transactions during non-working hours (2 AM to 4 AM) have a risk level of
medium.
+6. The default risk level is low.
```sql
UPDATE transaction_details
SET risk_level = CASE
- -- Transactions with dispute history or in high-risk regions
+ -- Transactions with a dispute history or in high-risk regions
WHEN has_dispute_history = TRUE THEN 'high'
WHEN transaction_region IN ('high_risk_region1', 'high_risk_region2') THEN
'high'
@@ -115,13 +115,13 @@ SET risk_level = CASE
-- Transactions during non-working hours
WHEN HOUR(transaction_time) BETWEEN 2 AND 4 THEN 'medium'
- -- Default risk
+ -- Default risk level
ELSE 'low'
END
WHERE transaction_date = '2024-11-24';
```
-The updated data is:
+The updated data is as follows:
```sql
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
@@ -135,6 +135,6 @@ The updated data is:
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
```
-## More Details
+## More Help
-For more detailed syntax on data updates, please refer to the
[UPDATE](../../sql-manual/sql-statements/Data-Manipulation-Statements/Manipulation/UPDATE)
command manual. You can also enter `HELP UPDATE` in the MySQL client command
line for more information and assistance.
+For more detailed syntax on data updates, please refer to the
[UPDATE](../../sql-manual/sql-statements/Data-Manipulation-Statements/Manipulation/UPDATE)
command manual. You can also enter `HELP UPDATE` in the MySQL client command
line for more help.
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/update/unique-update.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/update/unique-update.md
index 2cfd390f59..5344327d14 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/update/unique-update.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/update/unique-update.md
@@ -24,13 +24,13 @@ specific language governing permissions and limitations
under the License.
-->
-主要讲述如何使用 Update 命令来更新 Doris 中的数据。Update 命令只能在 Unique 数据模型的表中执行。
+主要讲述如何使用 Update 命令来更新 Doris 中的数据。Update 命令仅适用于 Unique 数据模型的表。
## 适用场景
-- 对满足某些条件的行，修改其取值
+- 小范围数据更新：适用于更新少量数据的场景，例如修复某些记录中的错误字段，或更新某些字段的状态（如订单状态更新等）。
-- 这个适合少量数据，不频繁的更新
+- ETL 批量加工部分字段：适用于大批量更新某个字段，常见于 ETL 加工场景。注意：大范围数据更新仅适合低频调用。
## 基本原理
@@ -44,7 +44,7 @@ Update 语法在 Doris 中是一个同步语法，即 Update 语句执行成功
Update 语句的性能和待更新的行数以及查询条件的检索效率密切相关。
-- 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。Update 更新比较合适偶发更新的场景，比如修改个别行的值。Update
并不适合大批量的修改数据。
+- 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。 对于小范围更新，Doris支持的频率与`INSERT INTO`语句类似，
对于大范围更新，由于单个update执行的时间较长， 仅适用于低频调用。
- 查询条件的检索效率：Update 实现原理是先将满足查询条件的行做读取处理，所以如果查询条件的检索效率高，则 Update
的速度也会快。条件列最好能命中索引或者分区分桶裁剪，这样 Doris 就不需要扫全表，可以快速定位到需要更新的行，从而提升更新效率。强烈不推荐条件列中包含
value 列。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/update/unique-update.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/update/unique-update.md
index 1120c3f174..4a2837b13a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/update/unique-update.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/update/unique-update.md
@@ -23,14 +23,13 @@ KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
-
-主要讲述如何使用 Update 命令来更新 Doris 中的数据。Update 命令只能在 Unique 数据模型的表中执行。
+主要讲述如何使用 Update 命令来更新 Doris 中的数据。Update 命令仅适用于 Unique 数据模型的表。
## 适用场景
-- 对满足某些条件的行，修改其取值
+- 小范围数据更新：适用于更新少量数据的场景，例如修复某些记录中的错误字段，或更新某些字段的状态（如订单状态更新等）。
-- 适合少量数据，不频繁的更新
+- ETL 批量加工部分字段：适用于大批量更新某个字段，常见于 ETL 加工场景。注意：大范围数据更新仅适合低频调用。
## 基本原理
@@ -44,7 +43,7 @@ Update 语法在 Doris 中是一个同步语法，即 Update 语句执行成功
Update 语句的性能和待更新的行数以及条件的检索效率密切相关。
-- 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。Update 更新比较适合偶发更新的场景，比如修改个别行的值。Update
并不适合大批量的修改数据。
+- 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。 对于小范围更新，Doris支持的频率与`INSERT INTO`语句类似，
对于大范围更新，由于单个update执行的时间较长， 仅适用于低频调用。
- 查询条件的检索效率：Update 实现原理是先将满足查询条件的行做读取处理，所以如果查询条件的检索效率高，则 Update
的速度也会快。条件列最好能命中索引或者分区分桶裁剪，这样 Doris 就不需要扫全表，可以快速定位到需要更新的行，从而提升更新效率。强烈不推荐条件列中包含
value 列。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/update/unique-update.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/update/unique-update.md
index c888a50dd8..4d73214d25 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/update/unique-update.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/update/unique-update.md
@@ -24,13 +24,13 @@ specific language governing permissions and limitations
under the License.
-->
-主要讲述如何使用 Update 命令来更新 Doris 中的数据。Update 命令只能在 Unique 数据模型的表中执行。
+主要讲述如何使用 Update 命令来更新 Doris 中的数据。Update 命令仅适用于 Unique 数据模型的表。
## 适用场景
-- 对满足某些条件的行，修改其取值
+- 小范围数据更新：适用于更新少量数据的场景，例如修复某些记录中的错误字段，或更新某些字段的状态（如订单状态更新等）。
-- 适合少量数据，不频繁的更新
+- ETL 批量加工部分字段：适用于大批量更新某个字段，常见于 ETL 加工场景。注意：大范围数据更新仅适合低频调用。
## 基本原理
@@ -44,7 +44,7 @@ Update 语法在 Doris 中是一个同步语法，即 Update 语句执行成功
Update 语句的性能和待更新的行数以及查询条件的检索效率密切相关。
-- 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。Update 更新比较适合偶发更新的场景，比如修改个别行的值。Update
并不适合大批量的修改数据。
+- 待更新的行数：待更新的行数越多，Update 语句的速度就会越慢。 对于小范围更新，Doris支持的频率与`INSERT INTO`语句类似，
对于大范围更新，由于单个update执行的时间较长， 仅适用于低频调用。
- 查询条件的检索效率：Update 实现原理是先将满足查询条件的行做读取处理，所以如果查询条件的检索效率高，则 Update
的速度也会快。条件列最好能命中索引或者分区分桶裁剪，这样 Doris 就不需要扫全表，可以快速定位到需要更新的行，从而提升更新效率。强烈不推荐条件列中包含
value 列。
diff --git a/versioned_docs/version-2.1/data-operate/update/unique-update.md
b/versioned_docs/version-2.1/data-operate/update/unique-update.md
index 1213a377d8..f0bda871a9 100644
--- a/versioned_docs/version-2.1/data-operate/update/unique-update.md
+++ b/versioned_docs/version-2.1/data-operate/update/unique-update.md
@@ -24,33 +24,33 @@ specific language governing permissions and limitations
under the License.
-->
-The main topic is how to use the UPDATE command to update data in Doris. The
UPDATE command can only be executed on tables in the Unique data model.
+This document explains how to use the `UPDATE` command to modify data in
Doris. The `UPDATE` command is only applicable to tables with a Unique data
model.
-## Use Cases
+## Applicable Scenarios
-- Modify the values of rows that meet certain conditions.
+- Small-scale data updates: Ideal for scenarios where a small amount of data
needs to be corrected, such as fixing erroneous fields in certain records or
updating the status of specific fields (e.g., order status updates).
-- Suitable for updating a small amount of data that is not frequently updated.
+- ETL batch processing of certain fields: Suitable for large-scale updates of
a specific field, commonly seen in ETL processing scenarios. Note: Large-scale
data updates should be infrequent.
-## Basic Principles
+## How It Works
-By utilizing the filtering logic of the query engine's WHERE clause, the
UPDATE command selects the rows that need to be updated from the target table.
Then, using the built-in logic of the Value column in the Unique model, the old
data is replaced with the new data, and the updated rows are reinserted into
the table, thus achieving row-level updates.
+The query engine uses its own filtering logic to identify the rows that need
to be updated. Then, using the Unique model's Value column logic, it replaces
old data with new data. The rows to be updated are modified and reinserted into
the table to achieve row-level updates.
### Synchronization
-The UPDATE syntax in Doris is synchronous, meaning that when an UPDATE
statement is executed successfully, the update operation is completed, and the
data is immediately visible.
+The `UPDATE` syntax in Doris is synchronous, meaning that once the `UPDATE`
statement is successfully executed, the update operation is completed and the
data is immediately visible.
### Performance
-The performance of the UPDATE statement depends on the number of rows to be
updated and the efficiency of the condition retrieval.
+The performance of the `UPDATE` statement is closely related to the number of
rows to be updated and the efficiency of the query conditions.
-- Number of rows to be updated: The more rows that need to be updated, the
slower the UPDATE statement will be. The UPDATE command is suitable for
scenarios where occasional updates are required, such as modifying values for
individual rows. It is not suitable for bulk data modifications.
+- Number of rows to be updated: The more rows that need updating, the slower
the `UPDATE` statement will be. For small-scale updates, Doris supports a
frequency similar to `INSERT INTO` statements. For large-scale updates, due to
the long execution time, it is only suitable for infrequent calls.
-- Efficiency of condition retrieval: The UPDATE operation reads the rows that
satisfy the condition first. Therefore, if the condition retrieval is
efficient, the UPDATE operation will be faster. It is recommended to have the
condition column indexed or utilize partitioning and bucketing pruning to
quickly locate the rows to be updated, thus improving the update efficiency. It
is strongly advised not to include the value column in the condition column.
+- Efficiency of query conditions: The `UPDATE` implementation first reads the
rows that meet the query conditions. Therefore, if the query conditions are
efficient, the `UPDATE` speed will be fast. It is best if the condition columns
can hit the index or partition bucket pruning, so Doris does not need to scan
the entire table and can quickly locate the rows that need updating, thereby
improving update efficiency. It is strongly recommended not to include value
columns in the condition columns.
-## Example
+## Usage Example
-Assuming in a financial risk control scenario, there is a transaction details
table with the following structure:
+Assume there is a transaction details table with the following structure in a
financial risk control scenario:
```sql
CREATE TABLE transaction_details (
@@ -70,7 +70,7 @@ UNIQUE KEY(transaction_id)
DISTRIBUTED BY HASH(transaction_id) BUCKETS 16
PROPERTIES (
"replication_num" = "3", -- Number of replicas, default is 3
- "enable_unique_key_merge_on_write" = "true" -- Enable MOW mode, support
merge update
+ "enable_unique_key_merge_on_write" = "true" -- Enable MOW mode, support
merge updates
);
```
@@ -88,20 +88,20 @@ The following transaction data exists:
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
```
-Update the risk level of all transactions on a daily basis according to the
following risk control rules:
-1. If there is a dispute history, the risk is high.
-2. If in a high-risk region, the risk is high.
-3. If the transaction amount is abnormal (more than 5 times the daily
average), the risk is high.
+Update the risk level of all daily transaction records according to the
following risk control rules:
+1. Transactions with a dispute history have a risk level of high.
+2. Transactions in high-risk regions have a risk level of high.
+3. Transactions with abnormal amounts (exceeding 5 times the daily average)
have a risk level of high.
4. Frequent transactions in the last 7 days:
- a. If the number of transactions > 50, the risk is high.
- b. If the number of transactions is between 20 and 50, the risk is medium.
-5. Transactions during non-working hours (2 AM to 4 AM), the risk is medium.
-6. The default risk is low.
+ a. Transactions > 50 times have a risk level of high.
+ b. Transactions between 20 and 50 times have a risk level of medium.
+5. Transactions during non-working hours (2 AM to 4 AM) have a risk level of
medium.
+6. The default risk level is low.
```sql
UPDATE transaction_details
SET risk_level = CASE
- -- Transactions with dispute history or in high-risk regions
+ -- Transactions with a dispute history or in high-risk regions
WHEN has_dispute_history = TRUE THEN 'high'
WHEN transaction_region IN ('high_risk_region1', 'high_risk_region2') THEN
'high'
@@ -115,13 +115,13 @@ SET risk_level = CASE
-- Transactions during non-working hours
WHEN HOUR(transaction_time) BETWEEN 2 AND 4 THEN 'medium'
- -- Default risk
+ -- Default risk level
ELSE 'low'
END
WHERE transaction_date = '2024-11-24';
```
-The updated data is:
+The updated data is as follows:
```sql
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
@@ -135,6 +135,6 @@ The updated data is:
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
```
-## More Details
+## More Help
-For more detailed syntax on data updates, please refer to the
[UPDATE](../../sql-manual/sql-statements/data-modification/DML/UPDATE) command
manual. You can also enter `HELP UPDATE` in the MySQL client command line for
more information and assistance.
\ No newline at end of file
+For more detailed syntax on data updates, please refer to the
[UPDATE](../../sql-manual/sql-statements/data-modification/DML/UPDATE) command
manual. You can also enter `HELP UPDATE` in the MySQL client command line for
more help.
diff --git a/versioned_docs/version-3.0/data-operate/update/unique-update.md
b/versioned_docs/version-3.0/data-operate/update/unique-update.md
index 1213a377d8..f0bda871a9 100644
--- a/versioned_docs/version-3.0/data-operate/update/unique-update.md
+++ b/versioned_docs/version-3.0/data-operate/update/unique-update.md
@@ -24,33 +24,33 @@ specific language governing permissions and limitations
under the License.
-->
-The main topic is how to use the UPDATE command to update data in Doris. The
UPDATE command can only be executed on tables in the Unique data model.
+This document explains how to use the `UPDATE` command to modify data in
Doris. The `UPDATE` command is only applicable to tables with a Unique data
model.
-## Use Cases
+## Applicable Scenarios
-- Modify the values of rows that meet certain conditions.
+- Small-scale data updates: Ideal for scenarios where a small amount of data
needs to be corrected, such as fixing erroneous fields in certain records or
updating the status of specific fields (e.g., order status updates).
-- Suitable for updating a small amount of data that is not frequently updated.
+- ETL batch processing of certain fields: Suitable for large-scale updates of
a specific field, commonly seen in ETL processing scenarios. Note: Large-scale
data updates should be infrequent.
-## Basic Principles
+## How It Works
-By utilizing the filtering logic of the query engine's WHERE clause, the
UPDATE command selects the rows that need to be updated from the target table.
Then, using the built-in logic of the Value column in the Unique model, the old
data is replaced with the new data, and the updated rows are reinserted into
the table, thus achieving row-level updates.
+The query engine uses its own filtering logic to identify the rows that need
to be updated. Then, using the Unique model's Value column logic, it replaces
old data with new data. The rows to be updated are modified and reinserted into
the table to achieve row-level updates.
### Synchronization
-The UPDATE syntax in Doris is synchronous, meaning that when an UPDATE
statement is executed successfully, the update operation is completed, and the
data is immediately visible.
+The `UPDATE` syntax in Doris is synchronous, meaning that once the `UPDATE`
statement is successfully executed, the update operation is completed and the
data is immediately visible.
### Performance
-The performance of the UPDATE statement depends on the number of rows to be
updated and the efficiency of the condition retrieval.
+The performance of the `UPDATE` statement is closely related to the number of
rows to be updated and the efficiency of the query conditions.
-- Number of rows to be updated: The more rows that need to be updated, the
slower the UPDATE statement will be. The UPDATE command is suitable for
scenarios where occasional updates are required, such as modifying values for
individual rows. It is not suitable for bulk data modifications.
+- Number of rows to be updated: The more rows that need updating, the slower
the `UPDATE` statement will be. For small-scale updates, Doris supports a
frequency similar to `INSERT INTO` statements. For large-scale updates, due to
the long execution time, it is only suitable for infrequent calls.
-- Efficiency of condition retrieval: The UPDATE operation reads the rows that
satisfy the condition first. Therefore, if the condition retrieval is
efficient, the UPDATE operation will be faster. It is recommended to have the
condition column indexed or utilize partitioning and bucketing pruning to
quickly locate the rows to be updated, thus improving the update efficiency. It
is strongly advised not to include the value column in the condition column.
+- Efficiency of query conditions: The `UPDATE` implementation first reads the
rows that meet the query conditions. Therefore, if the query conditions are
efficient, the `UPDATE` speed will be fast. It is best if the condition columns
can hit the index or partition bucket pruning, so Doris does not need to scan
the entire table and can quickly locate the rows that need updating, thereby
improving update efficiency. It is strongly recommended not to include value
columns in the condition columns.
-## Example
+## Usage Example
-Assuming in a financial risk control scenario, there is a transaction details
table with the following structure:
+Assume there is a transaction details table with the following structure in a
financial risk control scenario:
```sql
CREATE TABLE transaction_details (
@@ -70,7 +70,7 @@ UNIQUE KEY(transaction_id)
DISTRIBUTED BY HASH(transaction_id) BUCKETS 16
PROPERTIES (
"replication_num" = "3", -- Number of replicas, default is 3
- "enable_unique_key_merge_on_write" = "true" -- Enable MOW mode, support
merge update
+ "enable_unique_key_merge_on_write" = "true" -- Enable MOW mode, support
merge updates
);
```
@@ -88,20 +88,20 @@ The following transaction data exists:
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
```
-Update the risk level of all transactions on a daily basis according to the
following risk control rules:
-1. If there is a dispute history, the risk is high.
-2. If in a high-risk region, the risk is high.
-3. If the transaction amount is abnormal (more than 5 times the daily
average), the risk is high.
+Update the risk level of all daily transaction records according to the
following risk control rules:
+1. Transactions with a dispute history have a risk level of high.
+2. Transactions in high-risk regions have a risk level of high.
+3. Transactions with abnormal amounts (exceeding 5 times the daily average)
have a risk level of high.
4. Frequent transactions in the last 7 days:
- a. If the number of transactions > 50, the risk is high.
- b. If the number of transactions is between 20 and 50, the risk is medium.
-5. Transactions during non-working hours (2 AM to 4 AM), the risk is medium.
-6. The default risk is low.
+ a. Transactions > 50 times have a risk level of high.
+ b. Transactions between 20 and 50 times have a risk level of medium.
+5. Transactions during non-working hours (2 AM to 4 AM) have a risk level of
medium.
+6. The default risk level is low.
```sql
UPDATE transaction_details
SET risk_level = CASE
- -- Transactions with dispute history or in high-risk regions
+ -- Transactions with a dispute history or in high-risk regions
WHEN has_dispute_history = TRUE THEN 'high'
WHEN transaction_region IN ('high_risk_region1', 'high_risk_region2') THEN
'high'
@@ -115,13 +115,13 @@ SET risk_level = CASE
-- Transactions during non-working hours
WHEN HOUR(transaction_time) BETWEEN 2 AND 4 THEN 'medium'
- -- Default risk
+ -- Default risk level
ELSE 'low'
END
WHERE transaction_date = '2024-11-24';
```
-The updated data is:
+The updated data is as follows:
```sql
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
@@ -135,6 +135,6 @@ The updated data is:
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
```
-## More Details
+## More Help
-For more detailed syntax on data updates, please refer to the
[UPDATE](../../sql-manual/sql-statements/data-modification/DML/UPDATE) command
manual. You can also enter `HELP UPDATE` in the MySQL client command line for
more information and assistance.
\ No newline at end of file
+For more detailed syntax on data updates, please refer to the
[UPDATE](../../sql-manual/sql-statements/data-modification/DML/UPDATE) command
manual. You can also enter `HELP UPDATE` in the MySQL client command line for
more help.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
