Recursive update in Snowflake

Question

I have a table for different users in snowflake. I need to update Value column if there is a 'Z' value. Z would be replaced with the closest non-Z value from that user.

Original Table:

| User | Order | Value |
| ---- | ------| ----- |
| A    | 1     |  X    |
| A    | 2     |  Y    |
| A    | 3     |  Z    |
| A    | 4     |  Z    |
| A    | 5     |  W    |
| A    | 6     |  Z    |
| B    | 1     |  Y    |
| B    | 2     |  Z    |
| B    | 3     |  Z    |

Target Table:

| User | Order | Value |
| ---- | ------| ----- |
| A    | 1     |  X    |
| A    | 2     |  Y    |
| A    | 3     |  Y    |
| A    | 4     |  Y    |
| A    | 5     |  W    |
| A    | 6     |  W    |
| B    | 1     |  Y    |
| B    | 2     |  Y    |
| B    | 3     |  Y    |

I wrote a recursive update query. But the original table has millions of rows. It would require a very large amount of recursion, which is not allowed in Snowflake. Is there any other way that I could achieve my goal? I am thinking about window function, but don't have an idea of how to implement it.

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-01-11T01:06:44

You can use lag(ignore nulls) to get the previous value you want. Then you can use a join:

update original o
    set value = prev_value
    from (select o.*,
                 lag(case when value <> 'Z' then value end ignore nulls) over (partition by user order by order) as prev_value
          from original o
         ) o2
    where o.value = 'Z' and
          o2.user = o.user and
          o2.order = o.user

Recursive update in Snowflake

1 Answers