SQL updates a single row (Row by Row)
I am working in tsql and have a problem where I need to make multiple updates on one line based on multiple conditions.
- By rank in ascending order
- If the column is NULL, I need it to update no matter what the Rank is.
- If the rows are the same, I need to update it in the order T2_ID.
- And I need to use the latest updated output.
I tried using the update instruction below, but only the first update and the rest are ignored. Here's an example of the datasets I'm working with and the results I want.
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)
TABLE 1 :
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 output:
Fname Middle Lname Rank_
-------------------------------
John Mike Davis 1
+3
source to share
2 answers
One UPDATE statement can update each record only once.
So, if you understand correctly, the desired result is just one entry per Fname, using nonzero values for each column with the lowest rank, and in the case of links, the highest T2_ID?
You can achieve this simply by using subqueries:
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 that selection.
+2
source to share
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
0
source to share