Check duplicated value from temp table-CodePudding

I have a source table:

ID  Value
1   a
2   b
3   c
4   d

And a temp table #ABCD:

ID  Value
1   b
2   a
3   d

I want to update the original table from temp table based on Id match:

Original table:
ID  Value
1   b
2   a
3   d (duplicate)
4   d

So now my original table has a duplicate value (d)

So how to get the duplicate value (d) from temp table #ABCD? Before updating. Query:

CREATE TABLE [dbo].[Original](
    [Id] [int] NOT NULL,
    [Value] [nchar](10) NOT NULL
)
GO
INSERT INTO [dbo].[Original]
           ([Id]
           ,[Value])
VALUES
    (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd')
GO
CREATE TABLE #ABCD([Id] INT, [Value] nchar(10))
GO
INSERT INTO #ABCD([Id], [Value]) VALUES (1, 'b'), (2, 'a'), (3, 'd')

Result:

Duplicated value:
ID Value
3  d

CodePudding user response：

EDIT

Maybe this is better:

select q2.* from (
    select * from #ABCD 
    union 
    select * from original where id not in(select id from #ABCD)
    )q1
    inner join #ABCD  q2 
    on q1.Value = q2.Value 
    and q1.id<>q2.id

Try this. I did the update in another temp table and evaluate the final result.

DROP TABLE IF EXISTS #TMP

select * into #TMP 
from original

update t1
    set t1.Value = t2.value
from #TMP t1 
    inner join #ABCD t2 
        on t1.id=t2.id


select a.* from #TMP t1 
inner join #ABCD  t2 
    on t1.Value=t2.Value 
    and t1.id<>t2.id