Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update column based on other column value Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 3:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 1,429, Visits: 2,700
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
Post #1515806
Posted Tuesday, November 19, 2013 3:24 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 3,761, Visits: 8,439
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1515816
Posted Wednesday, November 20, 2013 9:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 1,429, Visits: 2,700
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
Post #1516098
Posted Wednesday, November 20, 2013 9:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 3,761, Visits: 8,439
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1516107
Posted Thursday, November 21, 2013 11:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 2,194, Visits: 3,304
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1516524
Posted Friday, November 22, 2013 10:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 467, Visits: 1,883
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.
Post #1516875
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse