Update table that references duplicates Sql Server

Question

I have two tables Table A and Table B. I need Table A to reference Table B but I first need to delete the duplicates of Table B and have Table A reference the new ID.

At the moment I am able to partition the records using WITH TIES but how do I ensure that each record points to the correct reference?

Example:

Table A

Name	RefId
Item 1	251
Item 2	251
Item 3	167
Item 4	75

question from:https://stackoverflow.com/questions/65623782/update-table-that-references-duplicates-sql-server

Answer 1

You can use UPDATE in tableA first then delete duplicate from TableB as follows:

update tableA A
set A.ref_id = 
     (select max(ref_id) 
        from tableB 
       where name in (select name 
                        from tableB B 
                       where B.ref_id = A.ref_id))

delete from tableB B
 where exists 
       (select 1 from tableB BB
         where BB.NAME = B.NAME
           AND BB.ref_id > B.ref_id)