Updates.kql

Updates.kql

// How to update data, try on https://aka.ms/adx.free.
// 1. sample table
.drop table MyTable ifexists 

.set MyTable <| 
datatable (id:int, fname:string, lname:string, age:int) [
    01,'John','Doe',21,
    02,'Jim','Brown',23
    ]

MyTable //2rows


// 2. append new value
.append MyTable <| 
datatable (id:int, fname:string, lname:string, age:int) [
    02,'James','Brown',23
    ]

MyTable //3rows

//arg_max
MyTable
| summarize arg_max(ingestion_time(),*) by id //ideally, you'd have a datetime col


// 3. add column
.alter table MyTable (id:int, fname:string, lname:string, age:int, enquedDate:datetime)  

.append MyTable <|
MyTable 
| project id, fname, lname, age, enquedDate=now()

MyTable
| summarize arg_max(enquedDate, *) by id


// 4. create materialized-view
.drop materialized-view CurrentMyTable ifexists

.create materialized-view with(backfill=true) CurrentMyTable on table MyTable
{
MyTable
| summarize arg_max(enquedDate, *) by id
}

CurrentMyTable


// 5. add more changes, use view to see update for id 3
.ingest inline into table MyTable <|
3, Laura, Fleitas, 32, 1/1/22
4, Hiram, Fleitas, 99, 1/1/22

CurrentMyTable

.append MyTable <|
MyTable
| where id == 3
| project id, fname='Ana Laura', lname, age, now()

MyTable

CurrentMyTable

// 6. Challenge: Can you update Hiram's age? :)