SQLTeam.com | Weblogs | Forums

How to update status to only repeated row not all rows?

I work on SQL server 2012 I face issue when I have two rows repeated on companyname and familyname it update two rows to repeated have temp table when two rows repeated
and this is wrong
correct and desired result i need is to update status to second row repeated to 'FamilyName With CompanyName Repeated'

    CREATE TABLE #TempTable
    (
    RowID INT PRIMARY KEY IDENTITY(1,1),
    FamilyName NVARCHAR(500),
    CompanyName NVARCHAR(500),
    [Status] Nvarchar(500)
    )

insert into  #TempTable	( [FamilyName], [CompanyName]) 
select 'MAX952','Maxim Integrated'
union all
select 'MAX952','Maxim Integrated'

    UPDATE T1
    SET T1.Status = 'FamilyName With CompanyName Repeated'
    FROM #TempTable T1
    INNER JOIN #TempTable T2 WITH(NOLOCK) ON  T1.RowID <> T2.RowID AND T1.FamilyName = T2.FamilyName AND T1. CompanyName = T2.CompanyName 

desired result

FamilyName CompanyName status
MAX952 Maxim Integrated NULL
MAX952 Maxim Integrated FamilyName With CompanyName Repeated

WRONG Result

FamilyName CompanyName status
MAX952 Maxim Integrated FamilyName With CompanyName Repeated
MAX952 Maxim Integrated FamilyName With CompanyName Repeated

I am not expert.
I tend to do this way

;with cte
as
( select *
, rn = row_number() over(partition by familyName, CompanyName order by familyName, CompanyName)
from  #TempTable t
)
update t 
set t.status = 'FamilyName With CompanyName Repeated'
from cte 
join #TempTable t on (cte.rowid = t.rowid)
where rn >1  

select *
from #temptable

thank you solved