Updating columns based on row number

Transact-SQL
1

Hi

How do I put this together correctly. I want to update the cost column with zero for the data that has row number greater than 1

update [dbo].[FlossbachWorkingDataloadFile]
set cost = 0
(
Select *,
ROW_NUMBER() over (Partition by [MasterIdentifier],[FundCode],[Cost] order by [MasterIdentifier],[FundCode],[Cost] ) as rowNumber
From [dbo].[FlossbachWorkingDataloadFile]
Where rowNumber > 1 )

2

even when i try this way

update X
set X.cost = 0

FROM
(
Select *,
ROW_NUMBER() over (Partition by [MasterIdentifier],[FundCode],[Cost] order by [MasterIdentifier],[FundCode],[Cost] ) as rowNumber
From [dbo].[FlossbachWorkingDataloadFile]
Where rowNumber > 1) X

I get an error saying rownumber is invalid column

3 


;WITH cte_delete AS (
    Select *,
    ROW_NUMBER() over (Partition by [MasterIdentifier],[FundCode],[Cost] order by [MasterIdentifier],[FundCode],[Cost] ) as rowNumber
    From [dbo].[FlossbachWorkingDataloadFile]
)
DELETE FROM cte_delete
WHERE rowNumber > 1
4

thanks for the quick reply.

I dont want to delete the records. I just want to update columns to 0 where there is more than 1 columns with the same masterid fundcode and cost value. can this be done with the rownumber

5

never mind i think i can just change it to update and it will work thanks

6

Oops, sorry, for some reason I was thinking DELETE, but, yeah, you can just do an UPDATE instead.