How to update table field if duplicate records found

Question

i am storing line item and xfundcode in a db table. some time line item and xfundcode is repeatedly stored in table. so i want to execute a query which will find duplicate records based on line item and xfundcode and set empty value to xfundcode from the subsequent records.

suppose i have 10 records and 3 records has same duplicate line item and xfundcode. so query should set empty value to xfundcode in two records and leave first duplicate records as it is. so tell me what query i need to execute.
thanks

i found one similar answer here https://stackoverflow.com/a/17784451/13722367

Answer 1

WITH CTE AS
(
    SELECT 
        row_number() over (partition by item order by Item) as id
        , xfundcode
    FROM MyTable
)
update CTE
SET xfundcode = NULL
WHERE ID <> 1

Answer 2

It sounds like yu are asking for something like this:

; WITH numbering AS (
     xfundcode, row_number() OVER(PARTITION BY lineitemno ORDER BY (SELECT NULL))
     FROM tbl
)
UPDATE numbering
SET     fundcode = NULL
WHERE  rowno > 1

Although, I am not entirely convinced that this is a wise thing to do...

Answer 3

You use a ranking function ROW_NUMBER() to return the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. And then you can update the values of the column with the sequential number greater than 1. This way you will not update the first row in each partition.

Answer 4

Hi @Sudip Bhatt ,

ROW_NUMBER function can sort data partitions(ROW_NUMBER (Transact-SQL))
Please refer to:

 update t  
 SET xfundcode = NULL  
 from (select xfundcode,row_number() over(partition by Item,xfundcode order by Item) as rn from MyTable) t  
 where rn>1  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo