Conditional row update

[smpa01]

I have a Kusto table like this, how can I update certain fields with where condition while doing inline ingestion

id	name	city	salary	tel
1	sam
2	john
3	tom

I can do the following. But by doing so, I need to provide the values for whole row; which i don't want. Also, I want to to be selective in picking columns to update

It does not have to be inline ingest thpough.

//KQL
.ingest inline into table emp <|
1,'sam','london','1000', null
,2,'john','ny','2000',null

//SQL equivalent
UPADTE dbo.people
SET salary = case id when 1 then 1000
                  id when 2 then 2000 END
 city = case id when 1 then 'london'
                  id when 2 then 'ny' END

Thank you in advance

[Anonymous · Accepted solution]

Hi @smpa01 ,

1.Create a temporary table with the updated values:

let tempTable = datatable (id:int, name:string, city:string, salary:int, tel:string)
[
    1, 'sam', 'london', 1000, null,
    2, 'john', 'ny', 2000, null
];

2.Merge the temporary table with the original table:

let emp = datatable (id:int, name:string, city:string, salary:int, tel:string)
[
    1, 'sam', '', '', '',
    2, 'john', '', '', '',
    3, 'tom', '', '', ''
];

let updatedEmp = union emp, tempTable
| summarize city = any(city), salary = any(salary), tel = any(tel) by id, name;

.set-or-replace emp <| updatedEmp

This approach allows you to selectively update specific columns without needing to provide values for the entire row. 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.