title: TSQL flag duplicate records with single unique id
body: <p>There has to be a better way to do this.</p>
  
  <p>I have a table that I flagged all the unique records like this:</p>
  
  <pre><code>    WITH
      CTE( TransId, OriginalName, StrippedName, RowNumber )
      AS
      (
          SELECT  TransID ,
                  Name ,
                  StrippedName,
                  RN = ROW_NUMBER() OVER ( PARTITION BY StrippedName ORDER BY StrippedName )
          FROM    dbo.Members
      )
  
  UPDATE  dbo.Members
  SET ParenId = TransID
  WHERE   TransID IN ( SELECT TransId FROM CTE WHERE RowNumber = 1 )
  </code></pre>
  
  <p>Now I would like to update all the duplicates records (where RowNumber > 1) to the unique ParentId.  Right now I'm using a UDF, which works, but it takes a very long time.  Here is the UDF:</p>
  
  <pre><code>CREATE FUNCTION dbo.getParentTransId ( @TransId INT, @strippedBusName VARCHAR(200) )
  RETURNS INT
  AS
  BEGIN
      DECLARE @ParentTransId INT
  
      SELECT  @ParentTransId = TransId
      FROM    dbo.Members
      WHERE   StrippedBusName = @strippedBusName
        AND   ParenId IS NOT NULL
  
      IF  @ParentTransId IS NULL
      BEGIN
          SET @ParentTransId = @TransId
      END
  
      RETURN @ParentTransId
  END
  GO
  </code></pre>
  
  <p>And here is the update statement I'm currently using:</p>
  
  <pre><code>UPDATE  dt
  SET dt.ParenId = dbo.getParentTransId ( dt.TransID, dt.StrippedBusName )
  FROM    dbo.Members dt
  GO
  </code></pre>
  
  <p>Is there a way to do the same update without using the UDF?</p>