SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Delete Query Help .....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldba20
Posting Yak Master

183 Posts

Posted - 11/27/2012 :  10:37:40  Show Profile  Reply with Quote
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

Edited by - sqldba20 on 11/27/2012 10:41:14

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  11:05:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/27/2012 :  15:26:24  Show Profile  Reply with 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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  15:38:49  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000