February 1, 2022 at 3:45 pm
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 ?
February 1, 2022 at 5:20 pm
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;
February 1, 2022 at 5:39 pm
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".
February 1, 2022 at 8:07 pm
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?
February 1, 2022 at 9:03 pm
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/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy