.update table command

Applies to: ✅ Azure Data Explorer

The .update table command performs data updates in a specified table by deleting and appending records atomically. There are two options for appending the records:

• Ingest the records based on a provided query. The query is noted using the AppendIdentifier.
• Move extents containing the records to append from another table to the target table. For more information, see Update using move extents.

Permissions

You must have at least Table Admin permissions to run this command.

Syntax

Learn more about syntax conventions.

Update using append and delete:

.update [async] table TableName delete DeleteIdentifier append AppendIdentifier [with ( propertyName = propertyValue )] <|
let DeleteIdentifier = DeletePredicate;
let AppendIdentifier = AppendPredicate;

Update using move extents and delete:

.update [async] table TableName delete DeleteIdentifier move SourceTableName [with ( *propertyName = propertyValue )] <|
let DeleteIdentifier = DeletePredicate;

Parameters

Supported properties

Returns

The result of the command is a table where each record represents an extent that was either created with new data or had records deleted.

Update using move extents

When you use this option, the records to append to target table are moved from the provided SourceTableName using move extents. The update moves all extents from the table, or only those that match the provided extentTagsToMove, if extentTagsToMove are specified in the command properties. The source table must be a local table in the current database and must the same schema as the target table.

The move extents option is useful when the records to append are already ingested to a staging table in the database, and should replace existing records in the target table. In this case, using the move extents option is more efficient than ingest from query, as this option doesn't require reingesting the records. See examples in Update rows from a staging table using move extents.

Choose between .update table and materialized views

There are scenarios where you could use either the .update table command or a materialized view to achieve the same goal in a table. For example, a materialized view could be used to keep the latest version of each record or an update could be used to update records when a new version is available.

Use the following guidelines to decide which method to use:

• If your update pattern isn't supported by materialized views, use the update command.
• If the source table has a high ingestion volume, but only few updates, using the update command can be more performant and consume less cache or storage than materialized views. This is because materialized views need to reprocess all ingested data, which is less efficient than identifying the individual records to update based on the append or delete predicates.
• Materialized views are a fully managed solution. The materialized view is defined once and materialization happens in the background by the system. The update command requires an orchestrated process (for example, Azure Data Factory, Logic Apps, and others) that explicitly executes the update command every time there are updates. If materialized views work enough for your use case, using materialized views requires less management and maintenance.

Performance tips

• Data ingestion is a resource-intensive operation that might affect concurrent activities on the cluster, including running queries. We recommend that you avoid the following resource-intensive actions: running many .update commands at once, and intensive use of the distributed property.
• Limit the append data to less than 1 GB per operation. If necessary, use multiple update commands.
• Set the distributed flag to true only if the amount of data being produced by the query is large, exceeds 1 GB, and doesn't require serialization: multiple nodes can then produce output in parallel. Don't use this flag when query results are small, since it might needlessly generate many small data shards.

Examples

For the examples, we're going to use the following table:

.set-or-replace Employees <|
  range i from 1 to 100 step 1
  | project Id=i
  | extend Code = tostring(dynamic(["Customer", "Employee"])[Id %2])
  | extend Color = tostring(dynamic(["Red", "Blue", "Gray"])[Id %3])

This command creates a table with 100 records starting with:

Update a single column on one row

The following example updates a single column on a single row:

.update table Employees delete D append A with(whatif=true) <|
    let D = Employees
      | where Id==3;
    let A = Employees
      | where Id==3
      | extend Color="Orange";

Notice that whatif is set to true. After this query, the table is unchanged, but the command returns that there would be an extent with one row deleted and a new extent with one row.

The following command actually performs the update:

.update table Employees delete D append A <|
    let D = Employees
      | where Id==3;
    let A = Employees
      | where Id==3
      | extend Color="Orange";

Update a single column on multiple rows

The following example updates on one single column Color to the value of Green on those rows that have the value blue.

.update table Employees delete D append A <|
    let D = Employees
        | where Code=="Employee"
        | where Color=="Blue";
    let A = D
      | extend Color="Green";

Here we reused the delete identifier in the definition on the append predicate.

Update multiple columns on multiple rows

The following example updates multiple columns on all rows with color gray.

.update table Employees delete D append A <|
    let D = Employees
      | where Color=="Gray";
    let A = D
      | extend Code=strcat("ex-", Code)
      | extend Color="";

Update rows using another table (reference values)

In this example, the first step is to create the following mapping table:

.set-or-replace ColorMapping <|
  datatable(OldColor:string, NewColor:string)[
    "Red", "Pink",
    "Blue", "Purple",
    "Gray", "LightGray",
    "Orange", "Yellow",
    "Green", "AppleGreen"
  ]

This mapping table is then used to update some colors in the original table:

.update table Employees delete D append A <|
  let D = Employees
    | where Code=="Customer";
  let A = D
    | lookup ColorMapping on $left.Color==$right.OldColor
    | project Id, Code, Color=NewColor

Update rows with a datatable

Sometimes values to update are known without being stored in a table, and the datatable operator can be helpful:

.update table Employees delete D append A <|
  let A = datatable(Id:long, Code:string, Color:string)[
    1, "Customer", "Purple",
    2, "Customer", "Magenta",
    3, "Customer", "Turquoise",
  ];
  let D = Employees
      | join kind=leftsemi A on Id
      | where true;

Update rows from a staging table using move extents

A popular pattern is to first land data in a staging table before updating the main table.

The first command creates a staging table:

.set-or-replace MyStagingTable <|
    range i from 70 to 130 step 5
    | project Id=i
    | extend Code = tostring(dynamic(["Customer", "Employee"])[Id %2])
    | extend Color = tostring(dynamic(["Red", "Blue", "Gray"])[Id %3])

The next command updates the main table with the data in the staging table, by moving all extents from staging table to main table:

.update table Employees delete D move MyStagingTable <|
    let D = Employees
        | join kind=leftsemi MyStagingTable on Id
        | where true;

Some records in the staging table didn't exist in the main table (that is, had Id>100) but were still inserted in the main table (upsert behavior). Since the command uses move extents, MyStagingTable will be empty after the update.

Update from a staging table using move extents with extent tags

In this example, the extents in the staging table include extent tags, and we're only interested in updating based on a subset of the tags:

.set-or-replace MyStagingTable with(tags='["drop-by:tag1"]')<|
    range i from 70 to 100 step 5
    | project Id=i
    | extend Code = tostring(dynamic(["Customer", "Employee"])[Id %2])
    | extend Color = tostring(dynamic(["Red", "Blue", "Gray"])[Id %3])

.set-or-replace MyStagingTable with(tags='["drop-by:tag2"]')<|
    range i from 100 to 150 step 5
    | project Id=i
    | extend Code = tostring(dynamic(["Customer", "Employee"])[Id %2])
    | extend Color = tostring(dynamic(["Red", "Blue", "Gray"])[Id %3])

The following command updates the main table with the data from the first ingestion ("drop-by:tag1") in the staging table. The delete part must filter on the extent tags as well, if you would like to only delete records based on this tag. The entire MyStagingTable is considered for the delete query, not only the records in the extents with "drop-by:tag1" so the filter must be explicitly added to the delete query.

.update table Employees delete D move MyStagingTable with (extentTagsToMove='["drop-by:tag1"]') <|
    let D = Employees | where Id in (MyStagingTable | where extent_tags() has "drop-by:tag1" | project Id);

At the end of the command, MyStagingTable includes only the records from the second ingestion ("drop-by:tag2"), as the command moves the extents with drop-by:tag1.

Compound key

The first command creates a table with compound keys:

.set-or-replace VersionedArticle <|
  datatable(ArticleId:string, Version:int, Detail:string)[
    "A", 1, "Early version",
    "B", 1, "News about mobiles",
    "C", 1, "Opinion article",
    "B", 2, "Revision about brand X",
    "B", 3, "Revision about brand Y",
    "C", 2, "Fact check"
  ]

The next command updates a specific record using the expanded syntax:

.update table VersionedArticle delete D append A <|
  let D = VersionedArticle
    | where ArticleId=="B"
    | where Version==3;
  let A = VersionedArticle
    | where ArticleId=="B"
    | where Version==3
    | extend Detail = "Revision about brand Z";

Related content

• Materialized views
• .delete table records - soft delete command