remove incomplete info from 3 tables or more

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)

remove incomplete info from 3 tables or more

Author

Topic

asafg
Starting Member

39 Posts

Posted - 2008-09-29 : 04:10:53

I have three tables each with unique ID.

I need to remove the records in all tables when the ID does not exist in all three tables.

Is there a way to do that in a more intelligent way than mine

delete form table2 where id not in (select id from table1)
delete form table3 where id not in (select id from table1)

delete form table1 where id not in (select id from table2)
delete form table3 where id not in (select id from table2)

delete form table1 where id not in (select id from table3)
delete form table2 where id not in (select id from table3)

I'm a beginner and I'm not even sure that what I wrote does the job correctly.

Thanks
Asaf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 04:18:57

[code]1.delete t from table1 t
inner join
(
select id
from
(select id,1 as cat from table1
union all
select id,2 from table2
union all
select id,3 from table3
)t
group by id
having count(distinct cat)<3
)r
on r.id=t.id

2.delete t from table2 t
inner join
(
select id
from
(select id,1 as cat from table1
union all
select id,2 from table2
union all
select id,3 from table3
)t
group by id
having count(distinct cat)<3
)r
on r.id=t.id

3.delete t from table3 t
inner join
(
select id
from
(select id,1 as cat from table1
union all
select id,2 from table2
union all
select id,3 from table3
)t
group by id
having count(distinct cat)<3
)r
on r.id=t.id[/code]

asafg
Starting Member

39 Posts

Posted - 2008-09-29 : 05:00:13

It's beautiful...

I think that the following change:

select id
into #T1 --the change
from
(select id,1 as cat from table1
union all
select id,2 from table2
union all
select id,3 from table3
)t
group by id
having count(distinct cat)<3
)r
on r.id=t.id

...can improve the performance for big tables (saving the repeating select queries)
Anyway I'll check if the change improve your code which I like very much.

Thanks
Asaf

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources