SQL Delete Query Help ..... - SQL Server Forums

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')

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]

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

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources