Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Delete Query Help .....

Author  Topic 

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-11-27 : 10:37:40
I have a table (#tblAccessList) which stores information about our Access to each group. For some groups the access type is 'Modify' and 'Read' in the table. I would like to delete the record which has 'Read' for that group and keep the 'Modify' because it is assumed that a group with Modify access also has read.

Here is the ddl, sample data and the output I would like to see:

create table #tblAccessList
(SecGrp varchar(100),
Access varchar(10))

insert into #tblAccessList values ('AdmAccess','Modify')
insert into #tblAccessList values ('AdmAccess','Read')
insert into #tblAccessList values ('AcctAccess','Read')
insert into #tblAccessList values ('FinAccess','Modify')
insert into #tblAccessList values ('DevAccess','Modify')
insert into #tblAccessList values ('DevAccess','Read')
insert into #tblAccessList values ('ProdAccess','Modify')
insert into #tblAccessList values ('TestAccess','Read')


OUTPUT:

SecGrp | Access
-----------------------
AdmAccess | Modify
AcctAccess | Read
FinAccess | Modify
DevAccess | Modify
ProdAccess | Modify
TestAccess | Read

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-27 : 11:04:45
[code]DELETE t
FROM #tblAccessList t
WHERE EXISTS
(
SELECT *
FROM #tblAccessList t2
WHERE t2.SecGrp = t.SecGrp
AND t2.Access = 'Modify'
)
AND t.Access = 'Read';[/code]
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 11:05:49
DELETE al
from #tblAccessList al
where al.access = 'read'
and exists
(select *
from #tblAccessList t
where al.SecGrp = t.SecGrp and t.Access = 'modify'
)


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-27 : 15:26:24
[code]Delete F from
(Select *,ROW_Number() OVER (PARTITION By SecGrp Order by Case When Access = 'Modify' then 1 Else 2 End) as Seq
from #tblAccessList
)F
Where F.Seq > 1[/code]
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 15:38:49
quote:
Delete F from
(Select *,ROW_Number() OVER (PARTITION By SecGrp Order by Case When Access = 'Modify' then 1 Else 2 End) as Seq
from #tblAccessList
)F
Where F.Seq > 1




Nice. That's way better than Sunita's

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -