.update table command

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

The .update table command performs data updates in a specified table by deleting and appending records atomically.

Permissions

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

Syntax

Learn more about syntax conventions.

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

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.

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, Power Automate, 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 with a staging table

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:

.update table Employees delete D append A <|
    let A = 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).

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