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: Today @ 2:45 PM
Points: 1,263, Visits: 2,392
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 2,763, Visits: 5,904
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 2:45 PM
Points: 1,263, Visits: 2,392
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 2,763, Visits: 5,904
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:04 PM
Points: 1,743, Visits: 2,545
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)
I'm not fat, I'm gravity challenged.
Post #1516524
Posted Friday, November 22, 2013 10:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 299, Visits: 1,193
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