SQL Update a Single Row Multiple Times (Row by Row)

Question

I'm working in tsql and have an issue where I need to do multiple updates to a single row based on multiple conditions.

1. By Rank in ascending order
2. If the column is NULL I need it to update no matter what the Rank is.
3. If the Ranks are the same I need it to update in order of T2_ID.
4. And I need it to use the last updated output.

I've tried using the update statement below but it only does the first update and the rest are ignored. Here is an example of the data sets I'm working with and the desired results.

Thanks in advance!

update a
set Middle = case when a.Rank_ >= b.Rank_ OR a.Middle IS NULL then ISNULL(b.Middle, a.Middle) end,
    LName = case when a.Rank_ >= b.Rank_ OR a.Lname IS NULL then ISNULL(b.LName, a.LName) end,
    Rank_ = case when a.Rank_ >= b.Rank_ then b.Rank_ end
from #temp1 a
inner join #temp2 b on a.fname = b.fname
where 
    b.T2_ID in (select top 100% T2_ID from #temp2 order by T2_ID asc)

TABLE1:

Fname   Middle  Lname   Rank_
------------------------------
John    NULL    NULL    2

Table2:

 T2_ID  Fname   Middle  Lname   Rank_
 --------------------------------------
    1   John    Mike    Doe      3
    2   John    NULL    Smith    1
    3   John    NULL    Davis    1

Desired result:

Fname   Middle  Lname   Rank_
-------------------------------
John    Mike    Davis   1

Answer 1

A single UPDATE statement can only update each record once.

So if I understand you correctly, the result you want is simply one record per Fname, using the non-null values for each column with the lowest rank and in case of ties, the highest T2_ID?

You could achieve this simply using subselects:

SELECT 
    Fname,

    (SELECT TOP 1 Middle FROM #temp1 b
     WHERE Middle IS NOT NULL AND b.Fname = a.Fname
     ORDER BY Rank_, T2_ID DESC) AS Middle

    (SELECT TOP 1 Lname FROM #temp1 b
     WHERE Lname IS NOT NULL AND b.Fname = a.Fname
     ORDER BY Rank_, T2_ID DESC) AS Lname

    (SELECT TOP 1 Rank_ FROM #temp1 b
     WHERE Rank_ IS NOT NULL AND b.Fname = a.Fname
     ORDER BY Rank_, T2_ID DESC) AS Rank_
FROM
    #temp1 a
GROUP BY
    Fname    /* To get one record per Fname */

You can then update #temp2 using the result of this select.