Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update column based on other column value


Update column based on other column value

Author
Message
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8506 Visits: 18115
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
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8506 Visits: 18115
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
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6674
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
sqldriver
sqldriver
Mr or Mrs. 500
Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)Mr or Mrs. 500 (584 reputation)

Group: General Forum Members
Points: 584 Visits: 2492
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search