UPDATE column based on whether it has NULL in a grouping

This might be very simple question, but I just can’t come up with a solution.

Here is an example

create table #Example
( 
    Key1 varchar(55) PRIMARY KEY,
    Key2 varchar(55),
    Key3 varchar(55),
    Key4 varchar(55)
)

insert into #Example
    select '1001', '100', NULL, NULL
    union all
    select '1002', '100', NULL, NULL
    union all
    select '1003', '200', NULL, NULL

Key1 is unique. We set Key3 by using following logic:

with UpdateKey3 as
(
    select 
        Key1, Key2,
        ROW_NUMBER() OVER (PARTITION BY Key2 ORDER BY Key1 DESC) as RN
    from 
        #Example
)
update ao
set Key3 = m.Key1
from #Example ao
inner join UpdateKey3 m ON ao.Key2 = m.Key2
where m.RN = 1

Table looks like this

I want Key4 to be:

Since any particular Key3 grouping has Key4 values all NULL, set it to Key3. Result should be this

Now, assume adding 1 more row:

insert into #Example
    select '1004', '100', NULL, NULL

And then we run UpdateKey3 query, result would become:

Now, we need to **** query, that will update Key4. Since new row is in Key3 group = 1004, and we already have previously filled Key4 for that group, NULL should become 1002. Final result

Aka, once we set Key4 for any particular Key3 grouping, Key4 should never change. What would be that **** query?

Answer

It’s a little hard to follow, but I think I see what you’re saying:

If key4 is null …

• Try to find another record with the same key3 value, and see what it says for key4.
• If any such record has a key4 value, use it
• Else just use the current record’s key 3 value

With this in mind, to update Key 4, you might want a procedure like this:

update    #Example
set       key4 = 
              isnull(
                  (
                      -- You have more than one row meeting your criteria.
                      -- Which do you choose.  I have a top 1, but order by what?
                      select 
                      top 1   key4 
                      from    #example sub 
                      where   #Example.key3 = sub.key3 
                      and     sub.key4 is not null
                  ), 
                  key3
              )
where     key4 is null

I stress ‘might’ because your statement (which I translated into the second bullet above):

Since new row is in Key3 group = 1004, and we already have previously filled Key4 for that group, NULL should become 1002.

doesn’t take into consideration the possibility that two previously existing key 3 groups of 1004 might have different key 4 values. Yes, in your example they’re both 1002, but in the future they won’t always be.

You’ll have to choose between one of them, and it’s not clear which one to choose. You’ll really have to nail that down before you put any such code into production.