Update

    • To modify the value of a row that meets certain conditions.
    • Point updates, small updates, where the rows to be updated are preferably a very small part of the entire table.
    • Only could be used in Unique table

    Explanation of terms

    1. Unique model: A data model in the Doris system. When the user imports rows with the same Key, the Value of the latter overrides the existing Value, in the same sense as Unique in Mysql.

    Use the query engine’s own where filtering logic to filter the rows that need to be updated from the table to be updated. Then use the Unique model’s own Value column replacement logic to change the rows to be updated and reinsert them into the table. This enables row-level updates.

    Suppose there is an order table in Doris, where order id is the Key column, order status, and order amount are the Value columns. The data state is as follows.

    At this time, after the user clicks the payment, Doris system needs to change the order id to ‘1’ order status to ‘pending shipment’, you need to use the Update function.

    After the user executes the UPDATE command, the system performs the following three steps.

    • Step 2: Change the order status of the row from ‘Pending Payment’ to ‘Pending Shipping’ (1, 100, ‘Pending shipment’)
    • Step 3: Insert the updated row back into the table to achieve the updated effect. | order id | order amount | order status | | —-| —-| —-| | 1 | 100| Pending Payment | | 1 | 100 | Pending shipments | Since the table order is a UNIQUE model, the rows with the same Key, after which the latter will take effect, so the final effect is as follows. | order id | order amount | order status | |—|—|—| | 1 | 100 | Pending shipments |

    Basic operations

    • value=xxx: The column to be updated, the left side of the equation must be the value column of the table. The right side of the equation can be a constant or an expression transformation of a column in a table. For example, if value = 1, then the value of the column to be updated will be 1. For example, if value = value + 1, the value of the column to be updated is incremented by 1.

    • condition: Only rows that satisfy the condition will be updated. condition must be an expression that results in a Boolean type. For example, if k1 = 1, only rows with a k1 column value of 1 will be updated. For example, if k1 = k2, only rows with the same value in column k1 as in column k2 will be updated. No support for unfilled condition, i.e., no support for full table updates.

    The Update syntax is a synchronization syntax in Doris. If the Update statement succeeds, the update succeeds and the data is visible.

    The performance of the Update statement is closely related to the number of rows to be updated and the retrieval efficiency of the condition.

    • Number of rows to be updated: The more rows to be updated, the slower the Update statement will be. This is consistent with the principle of importing. Doris updates are more suitable for occasional update scenarios, such as changing the values of individual rows. Doris is not suitable for large batches of data changes. Large modifications can make Update statements take a long time to run.

    By default, multiple concurrent Update operations on the same table are not allowed at the same time.

    The main reason for this is that Doris currently supports row updates, which means that even if the user declares , virtually all other Value columns will be overwritten (even though the values are not changed).

    This presents a problem in that if two Update operations update the same row at the same time, the behavior may be indeterminate. That is, there may be dirty data.

    However, in practice, the concurrency limit can be turned on manually if the user himself can guarantee that even if concurrent updates are performed, they will not operate on the same row at the same time. This is done by modifying the FE configuration . When the configuration value is true, there is no limit on concurrent updates.

    Since Doris currently supports row updates and uses a two-step read-and-write operation, there is uncertainty about the outcome of an Update statement if it modifies the same row as another Import or Delete statement.

    Version

    Doris Version 0.15.x +