SQL多次更新单行（逐行）

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

我正在使用tsql并且遇到一个问题，我需要根据多个条件对单行进行多次更新。

By Rank in ascending order
按升序排列
If the column is NULL I need it to update no matter what the Rank is.
如果列为NULL，无论Rank是什么，我都需要更新。
If the Ranks are the same I need it to update in order of T2_ID.
如果等级相同，我需要按T2_ID的顺序更新。
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:

表格1：

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

Table2:

表2：

 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

2 个解决方案

#1

A single UPDATE statement can only update each record once.

单个UPDATE语句只能更新一次记录。

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?

因此，如果我理解正确，您想要的结果只是每个Fname一个记录，对于每个列的最低等级使用非空值，如果是关系，则最高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.

然后，您可以使用此选择的结果更新＃temp2。

#2

You also user group by and having 



    create table #temp1(

    fname varchar(10),
    mname varchar(10),
    lname varchar(10),
    rank_ int
)
 create table #temp2(
    t2id int,
    fname varchar(10),
    mname varchar(10),
    lname varchar(10),
    rank_ int
)

insert into #temp1
select 'john',null,null,2 

insert into #temp2
select 1,'john','mike','doe',3 union all 
select 2,'john',null,'smith',1 union all 
select 3,'john',null,'davis',1 


select top 1 fname,(select top 1 mname from #temp2 order by rank_ desc) as mname,lname,min(asrank_) from (
select b.rank_,a.fname,case when a.Rank_ >= b.Rank_ OR a.mname IS NULL then ISNULL(b.mname, a.mname) end as mname,
    case when a.Rank_ >= b.Rank_ OR a.Lname IS NULL then b.LName end as lname,
    case when a.Rank_ >= b.Rank_ then b.Rank_ end asrank_
from #temp1 a
inner join #temp2 b on a.fname = b.fname)tbl group by fname,mname,lname having min(asrank_)=1



drop table #temp2
drop table #temp1

#1