Delete duplicates, and reassign FK value in SQL

Question

I have two tables:

 Table1: 
 [id], [ColA]

 Table2: 
 [id], [Table1Id]

A previous update caused duplicates in Table1 and those duplicates to be assocated with rows in Table2. It looks like this in the db:

Table1
1, 89
2, 89

Table2
6, 1
7, 2

I would like to delete the duplicates and reassign the value of the Table2.Table1Id to the one Table1.id row that should be left. Is this possible in a single statement? Ive seen this post on how to delete all but one, but I am lost on how to reassign the Table2.Table1Id value.

edit: The end result should look like this:

Table1
2, 89

Table2
6, 2
7, 2

No, ColA represents the duplicates I'm trying to get rid of, but yes, once I delete the duplicates, it should be unique as well. — Mike_G, Jun 07 '17 at 19:57

score 0 · Answer 1 · answered Jun 07 '17 at 20:13

drop table if exists dbo.Table2;
drop table if exists dbo.Table1;

create table dbo.Table1 (
ID int primary key
, ColA int
);

create table dbo.Table2 (
ID int
, Table1ID int
);

alter table dbo.Table2
add constraint FK_Table2_Table1
foreign key (Table1ID)
references dbo.Table1 (ID);

insert into dbo.Table1 (ID, ColA)
values (1, 89), (2, 89);

insert into dbo.Table2 (ID, Table1ID)
values (6, 1), (7, 2);

drop table if exists #temp;

select
ttt.ID
into #temp
from (
select
    t.ID
    , t.ColA
    , ROW_NUMBER() over (order by t.ID) as Rbr
from dbo.Table1 t
    inner join (
        select
            t.ColA
        from dbo.Table1 t
        group by t.ColA
        having COUNT (t.ID) > 1
    ) tt on t.ColA = tt.ColA
) ttt
where ttt.Rbr > 1

update t2
set
Table1ID = t1i.ID
from #temp t
inner join dbo.Table2 t2 on t.ID = t2.Table1ID
inner join dbo.Table1 t1 on t.ID = t1.ID
inner join dbo.Table1 t1i on t1.ColA = t1i.ColA
        and t1i.ID not in (select t.ID from #temp t)

delete t1
from #temp t
inner join dbo.Table1 t1 on t.ID = t1.ID

select
*
from dbo.Table1 t

select
*
from dbo.Table2 t

Delete duplicates, and reassign FK value in SQL

1 Answers1