I know that in ADX I can't update an existing row because it's an append-only system. I want to add a specific row with a condition: if there is no other row inside the table with the same values on certain columns.
I came up with this logic but I think this can be done much simpler:
.set-or-append target_table <|
let exists_row_count = old_table | where field1 == value1 and field2 == value2 | count()
let appended_row = case(exists_row_count == 0, <the whole record>, <empty record or null>)
appended_row
*need to mention that I get the value1, value2, and when I'm using a logic app here and I can iterate on each and every new record that I want to insert into the table, and of course, that the record is in tabular form.
You can create a Materialized View on top of your original table, and dedup by the columns you choose.