Multiple delete.. - 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 2000 Forums

Transact-SQL (2000)

Multiple delete..

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2006-01-06 : 08:33:46

Hi all,

I have a security table where there are three columns, docnumber, personorgroup and permissions. I need to find all documents which have personorgroup A, personorgroup B and personogroup C, then delete personogroup C from the table. Data structure as below..

Docnumber, Personorgroup, Permissions
1011 A all
1011 B all
1011 C all

So, if the document has all three personogroups assigned to it, I need to delete personorgroup C. Will this be somekind of conditional delete?

Thanks guys..

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 08:43:12

delete tbl
from tbl
join
(select Docnumber from tbl where Personorgroup in ('A','B') group by DocNumber having count(distinct Personorgroup) = 2 ) a
on tbl.Docnumber = a.Docnumber
where tbl.Personorgroup = 'C'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 08:45:11

[code]Declare @t table(Docnumber int, Personorgroup char(1), Permissions char(3))
Insert into @t
select 1011, 'A', 'all' union all
select 1011, 'B', 'all' union all
select 1011, 'C', 'all' union all
select 1012, 'A', 'all' union all
select 1012, 'B', 'all' union all
select 1012, 'C', 'all'

Delete from @t where exists(
Select docnumber from @t where Personorgroup in ('A','B','C')
group by docnumber having count(*)=3)
and Personorgroup='C'

select * from @t
[/code]

Madhivanan

Failing to plan is Planning to fail

cblatchford
Starting Member

40 Posts

Posted - 2006-01-06 : 09:07:27

Legendary thanks very much NR and madhivanan; although i went with NR solutions in the end. NR, any chance you could provide a brief explanation as to how that query works? I understand most of it, but it would be usefull for future reference..

Thanks so much again chaps

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 10:59:12

select Docnumber from tbl where Personorgroup in ('A','B') group by DocNumber having count(distinct Personorgroup) = 2

The subquery gets all doc numbers which have 2 distinct entries for Personorgroup in ('A','B') - i.e. have both A and B entries.
This is joined to the table to delete all the C entries for those doc numbers - i.e. deletes the C entry for everythnig where there is an A and B entry.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cblatchford
Starting Member

40 Posts

Posted - 2006-01-06 : 11:25:42

Hi,

Great stuff; so could I replace the ('A','B') with mulitple values such as below..

select Docnumber from tbl where Personorgroup in ('a','b','d','e') group by DocNumber having count(distinct Personorgroup) = 2

Would this then look for any combination of a and c, b and c, d and c or e and c?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 12:09:18

That would delete the C's if there were two of A, B, D, E.
i.e. would make sure there wasn't a C if there aer two other entries.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources