choom choom - 2 months ago 11
SQL Question

update if combination of 2 columns does not exist

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

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
  1. 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
  1. 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
Comments