﻿UPDATE t SET 
   C_NEV = C_NEV+' ('+cast(rn as varchar(3))+')'
  ,LASTCHANGED = GETDATE()
  ,MODIFIER = 0
FROM T_TEREM t
  INNER JOIN (
  SELECT ROW_NUMBER() OVER (partition by  t.C_INTEZMENYID,t.C_TANEVID,t.C_NEV ORDER BY ID) rn,t.ID FROM T_TEREM t
    INNER JOIN (
      SELECT 
         C_INTEZMENYID
        ,C_TANEVID
        ,C_NEV 
      FROM T_TEREM 
      WHERE TOROLT='F'
      GROUP BY C_INTEZMENYID , C_TANEVID , C_NEV
      HAVING COUNT(*)>1) x ON x.C_INTEZMENYID=t.C_INTEZMENYID AND x.C_TANEVID=t.C_TANEVID AND x.C_NEV=t.C_NEV
  WHERE t.TOROLT='F') t2 ON t2.ID=t.ID AND t2.rn>1

DROP INDEX IF EXISTS T_TEREM.NCU_Terem_IntezmenyId_TanevId_Nev_MukodesiHelyId

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_Terem_IntezmenyId_TanevId_Nev') BEGIN
  CREATE UNIQUE NONCLUSTERED INDEX NCU_Terem_IntezmenyId_TanevId_Nev
    ON dbo.T_TEREM (C_INTEZMENYID , C_TANEVID , C_NEV)
  WHERE TOROLT='F'
  WITH (FILLFACTOR = 80)
END


