Update column based on other column value

Question

Post reply

Update column based on other column value

Robert Klimes

SSCoach

Points: 17865
More actions
November 19, 2013 at 3:05 pm

#301554

I am looking for a way to update a column for only one row for each ID based on a predefined ranking order.
I have a table with multiple rows per ID, each row will have position column, a column with the ranked values and a flag. the flag should only be set for one row per ID and for the row that has the highest ranking. This is what I have come up with that achieves the result but I am wondering if there is a more concise way getting the same result.
create table #tmp (ID int, pos int, ltr char(1),flag char(1))
insert into #tmp(ID,pos,ltr)
values(1,1,'A'),(1,2,'B'),(2,1,'A'),(3,1,'B'),(3,2,'C'),(3,3,'A'),(4,1,'B'),(5,1,'C'),(5,2,'B'),(6,1,'C'),(7,1,'D'),(7,2,'B'),(7,3,'C'),(7,4,'A')
with cte as(
select ID,
CASE WHEN ltr = 'A' then 1
WHEN ltr = 'B' then 2
WHEN ltr = 'C' then 3
WHEN ltr = 'D' then 4
END AS rank_type,
ltr,
POS
from #tmp),
min_rank as (select ID,min(rank_type) min_rank
from cte
group by ID),
updt as(
select a.ID,a.pos
from cte a
inner join min_rank b
on a.ID = b.ID
and a.rank_type = b.min_rank)
update t
set flag = 'Y'
from #tmp t
inner join updt u
on t.ID = u.ID
and t.pos = u.pos
SELECT *
FROM #tmp
drop table #tmp
Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Here's a more concise way to do it. You can update the table by updating the CTE.;-)

WITH CTE AS(

SELECT *, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ltr) rn

FROM #tmp

)

UPDATE CTE SET

flag = 'Y'

WHERE rn = 1;

If your rank is not ordered alphabetically, you can simply change the column with the CASE statement in the ORDER BY clause

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Robert Klimes SSCoach Points: 17865 More actions · Answer 2

awesome, that did the trick and it's cleaner. I was unaware that you could update CTEs directly. Always a bonus to learn something new.

Thanks Luis.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You're welcome. I was gladly surprised the first time I realized that I could update/delete a CTE (is mostly like views).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 100947 More actions · Answer 4

Of course not all CTEs are going to be updateable, it will depend on the specific coding of the CTE ;-).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

sqldriver SSChampion Points: 10440 More actions · Answer 5

Luis Cazares (11/19/2013)
Here's a more concise way to do it. You can update the table by updating the CTE.;-)

Put this on the list of things I'm mad I didn't know.