update if combination of 2 columns does not exist

Question

I have 2 tables.

group_owner

group_id  |   user_id
--------- | ----------
  51      |     1
  51      |     2
  51      |     3
  52      |     2
  53      |     4

users

user_id   |   username
--------- | -----------
   1      |    John
   2      |    John
   3      |    John
   4      |    Peter

user_id of group_owner has foreign key relation with user_id of users.

I am removing duplicates from users table. I need to keep the user with lowest id if there are duplicates and delete rest of them. I created a temporary table as shown below.

SELECT
  t2.id    AS Old_id,
  t1.MinID AS New_id
INTO #Translation
FROM (SELECT
        MIN(id) AS MinID,
        username
      FROM users
      GROUP BY username) t1
  INNER JOIN users t2
    ON t1.username = t2.username

I need to replace Old_ids with New_ids in group_owner table. group_owner has constraint on group_id and user_id.

ALTER TABLE [dbo].[group_owner] ADD  CONSTRAINT [pk_group_owner] PRIMARY KEY CLUSTERED 
(
    [group_id] ASC,
    [user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

If that combination already exists it will blow up. So, doing the following will not work.

UPDATE group_owner 
  SET group_owner.[user_id] = New_id
  FROM group_owner 
    INNER JOIN #Translation T
      ON group_owner.[user_id] = T.Old_id
  WHERE T.Old_id <> T.New_id

Can someone help me in updating group_owner if that combination does not exists and deleting the combination if that combination exists?

Following is the group_owner and users tables should look like after processing:

group_owner

group_id  |   user_id
--------- | ----------
  51      |     1
  52      |     1
  53      |     4

users

user_id   |   username
--------- | -----------
   1      |    John
   4      |    Peter

Reference: http://weblogs.sqlteam.com/jeffs/archive/2004/10/07/2190.aspx

Answer 1

Try with the below steps.

1. Deleting the unwanted records from the group_owner table.

   As you said group_id and user_id combination is unique we need only one entry for group_id =51 and user_id =1 ,so we can remove (51,2) and (51,3) combinations from group_owner .

Script :

;with cte_1
    as
    (SELECT Username,[user_id],COUNT([user_id])OVER(PARTITION BY Username Order by [Username]) as CNT
    FROM users  )
    ,cte_2
    as
    (SELECT Username,a.[user_id],b.group_id  , ROW_NUMBER()OVER(PARTITION BY b.group_id Order by [user_id]) as Rno
    FROM cte_1 a
    JOIN group_owner b on a.[user_id]=b.[user_id]
    WHERE CNT>1)
    DELETE c3
    FROM cte_2 c2
      JOIN group_owner c3 on c2.group_id=c3.group_id and c2.[user_id]=c3.[user_id]
    WHERE Rno>1

2. Next step is to update the [user_id] in the group_owner table with the Minimum user_id from user table.

Script:

;with cte_1
as
(SELECT Username,[user_id]
 ,COUNT([user_id])OVER(PARTITION BY Username Order by [Username]) as CNT
 ,MIN([user_id])OVER(PARTITION BY Username Order by [user_id]) as [MINUserID]
FROM users  )
UPDATE b
  SET b.user_id=a.[MINUserID]
FROM cte_1 a
 JOIN group_owner b on a.[user_id]=b.[user_id]
 WHERE CNT>1

3. Third and final step is to remove the duplicate entries from the User table by keeping minimum value of user id.

Script :

;with cte_1
    as
    (SELECT Username,[user_id],ROW_NUMBER()OVER(PARTITION BY Username Order by [user_id]) as RNO
    FROM users  )
    DELETE 
    FROM cte_1
    WHERE RNO>1