CODEWITHSUNDEEP
sqlsql-serveron-duplicate-key
Ramveer Singh
Ramveer Singh

Reputation: 39

replacing Duplicate rows in a table and in tables having foreign key reference to the 1st table

I have 2 tables table 1: teams with column Teamid ,teamName which has some duplicates like 

   Table 1    TeamID  | TeamName
-------------------------------------
                1     | abc
                2     | abc

table 2 :UsedTeams with column ID ,requestID ,teamdID(foreign key teams(teamid))

Table 2
ID |Requestid  | teamID
---|-----------|--------
 1 | 1000      | 1
 2 | 1001      | 2

what i want is to remove duplicate teams from teams table like removing team with teamid 2 and also which row in Used team has teamid equal to 2 should get updated to teamid 1.

Upvotes: 0

Views: 63

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

A CTE and the OVER-clause helps to simplify the task:

WITH CTE AS
(
    SELECT t.*, 
           RN = ROW_NUMBER() OVER (PARTITION BY TeamName ORDER BY TeamId),
           MinTeamId = MIN(TeamId) OVER (PARTITION BY TeamName)
    FROM Teams t
)
UPDATE ut SET ut.TeamId = CTE.MinTeamId 
FROM CTE INNER JOIN UsedTeams ut ON CTE.TeamId = ut.TeamId
WHERE CTE.RN > 1

Upvotes: 1

Related Questions