Snippets

Ricardo Chu Remove dups in lportal.ResourcePermission table

You are viewing an old version of this snippet. View the current version.
Revised by Ricardo Chu bfe139f
ResourcePermission-dups.sql
Raw 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/*
Duplicate rows in lportal.ResourcePermission
dev: The duplicate key value is (10154, 145, 4, 10524_LAYOUT_145, 10163).
create unique index IX_8D83D0CE on lportal.ResourcePermission (companyId, name, scope, primKey, roleId);
*/


select companyId, name, scope, primKey, roleId,ownerId,actionIds,count(*) dup_count
from lportal.ResourcePermission
group by companyId, name, scope, primKey, roleId,ownerId,actionIds
having count(*) > 1
;
select companyId, name, scope, primKey, roleId,count(*) dup_count
from lportal.ResourcePermission
group by companyId, name, scope, primKey, roleId
having count(*) > 1


/* identify the duplicates */
WITH CTE AS(
   SELECT companyId, name, scope, primKey, roleId,ownerId,actionIds,
       RN = ROW_NUMBER()OVER(PARTITION BY companyId, name, scope, primKey, roleId,ownerId,actionIds ORDER BY companyId, name, scope, primKey, roleId,ownerId,actionIds)
   FROM lportal.ResourcePermission
)
select * FROM CTE WHERE RN > 1
;

/* delete the duplicates */
WITH CTE AS(
   SELECT companyId, name, scope, primKey, roleId,ownerId,actionIds,
       RN = ROW_NUMBER()OVER(PARTITION BY companyId, name, scope, primKey, roleId,ownerId,actionIds ORDER BY companyId, name, scope, primKey, roleId,ownerId,actionIds)
   FROM lportal.ResourcePermission
)
delete FROM CTE WHERE RN > 1
;

/* backup the table */
select * 
into lportal.ResourcePermission_tempchu
from lportal.ResourcePermission
HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.