Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago
Solved

Conditional row update

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

 

KQL Database
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.