Updating rows among same table in Azure Synapse Analytics

Question

I want to update Id column on basis of email and first_name. For Example, in the below table i want to update 'A0538_0' to the remaining two rows for the combination of email and firstname.

Required Output:

|id     | email           | firstname|
|------------------------------------|
|A0538  |krishna@gmail.com|Krish     |
|A0538_0|krishna@gmail.com|Surya     |
|A0538_0|krishna@gmail.com|Surya     |
|A0538_0|krishna@gmail.com|Surya     |

Note: Prev column is rank() window function i applied. Can Anyone please me here?

Answer 1

You could use window functions in an updable CTE:

with cte as (
    select id,
        min(id) over(partition by email, firstName) min_id
    from mytable t
)
update cte set id = min_id where id != min_id

This defines the "first" id as the minimum value; if you have a different ordering column, say ordering_id, then first_value is more appropriate:

with cte as (
    select id,
        first_value(id) over(
            partition by email, firstName
            order by ordering_id
        ) first_id
    from mytable t
)
update cte set id = first_id where id != first_id