Update table based on Rownumber

Question

Post reply

Update table based on Rownumber

komal145

SSCrazy Eights

Points: 9918
More actions
February 1, 2022 at 3:45 pm

#3979650

I have a table Table A with a flag like below. I am getting the latest record for ID based on latest date. Something like below.
But I need to update my table column FLAG based on my query results where row_number=1 the 'Y' else 'N'.
Is there a way to update , table based on query results using merge ?

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

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

DesNorton SSC-Insane Points: 24399 More actions · Answer 1

You have not provided any usable data or a base query to be able to test against.

The following code should get you moving in the right direction

WITH cteData as (
  SELECT ID
       , ROW_Num
       , FLAG
  FROM   TableA
)
UPDATE cteData
SET FLAG = CASE WHEN ROW_Num = 1 THEN 'Y' ELSE 'N' END;

How to post data/code on a forum to get the best help.

ScottPletcher SSC Guru Points: 101118 More actions · Answer 2

I'm guessing you don't already have a row_num.

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS row_num
    FROM dbo.TableA
)
UPDATE cte_add_row_num
SET FLAG = CASE WHEN row_num = 1 THEN 'Y' ELSE 'N' END

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".

kaj SSCommitted Points: 1607 More actions · Answer 3

komal145 wrote:

I have a table Table A with a flag like below. I am getting the latest record for ID based on latest date. Something like below.
But I need to update my table column FLAG based on my query results where row_number=1 the 'Y' else 'N'.
Is there a way to update , table based on query results using merge ?

Your reference to a merge confuses me. Are you doing a MERGE operation and need to update the resulting merged output on the fly?

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

If you are using the ROW_NUMBER function to find a specific row and perform an update, then it will likely not work.

The row_number function will return a different row if an insert or update has occurred on the table. But, without sample code, we can't really tell if that is true.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/