Update column based on other column value

Question

Post reply

Update column based on other column value

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

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

Luis Cazares SSC Guru Points: 183706 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: 183706 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: 101207 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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.