Cleaning up Duplicate SQL Rows

So recently I’ve been tasking with cleaning up an SQL database for a developing project I’ve been working on for EPIC and User Provisioning. I had two end up merging two table sets together to get the data I needed, However, duplicate rows impact the process, So I used the following commands below to filter through the Datasets and find the Duped rows to delete all but just one of the instances.

SELECT     COUNT(*) AS Expr2, Dept, JobCode, RightsItem, RightsValue AS Expr1
FROM         (SELECT     TOP (100) PERCENT AppName, Dept, JobCode, RightsItem, RightsValue, Entity
                       FROM          dbo.AppRights
                       WHERE      (AppName LIKE 'EPIC')) AS derivedtbl_1
GROUP BY Dept, JobCode, RightsItem, RightsValue
HAVING      (COUNT(*) &gt; 1)
SELECT     AppName, Dept, JobCode, RightsItem, RightsValue, Entity, ID
FROM         dbo.AppRights
WHERE     (ID IN
                          (SELECT     id AS Expr1
                            FROM          (SELECT     TOP (100) PERCENT AppName, Dept, JobCode, RightsItem, RightsValue, Entity, ID
                                                    FROM          dbo.AppRights AS AppRights_1
                                                    WHERE      (AppName LIKE 'EPIC')
                                                    ORDER BY RightsItem) AS derivedtbl_1
                            GROUP BY Dept, JobCode, RightsItem, RightsValue
                            HAVING      (COUNT(*) &gt; 1)))
WITH g AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY AppName, Dept, JobCode, RightsItem, RightsValue, Entity order by Dept) AS row
    FROM dbo.AppRights)
delete FROM g
WHERE row &gt; 1

SELECT COUNT(*) AS Expr2, Dept, JobCode, RightsItem, RightsValue AS Expr1

FROM (SELECT TOP (100) PERCENT AppName, Dept, JobCode, RightsItem, RightsValue, Entity

FROM dbo.AppRights

WHERE (AppName LIKE 'EPIC')) AS derivedtbl_1

GROUP BY Dept, JobCode, RightsItem, RightsValue

HAVING (COUNT(*) > 1)

SELECT AppName, Dept, JobCode, RightsItem, RightsValue, Entity, ID

FROM dbo.AppRights

WHERE (ID IN

(SELECT id AS Expr1

FROM (SELECT TOP (100) PERCENT AppName, Dept, JobCode, RightsItem, RightsValue, Entity, ID

FROM dbo.AppRights AS AppRights_1

WHERE (AppName LIKE 'EPIC')

ORDER BY RightsItem) AS derivedtbl_1

GROUP BY Dept, JobCode, RightsItem, RightsValue

HAVING (COUNT(*) > 1)))

WITH g AS (

SELECT ROW_NUMBER() OVER(PARTITION BY AppName, Dept, JobCode, RightsItem, RightsValue, Entity order by Dept) AS row

FROM dbo.AppRights)

delete FROM g

WHERE row > 1

Leave a comment

Cancel reply