July 8, 2005 at 6:28 am
HI,
I have a large sql table and two smaller ones. The two smaller ones are derived from the big one. Say table A is the big one, I wish to removed all the rows from table A that are contained in B and C.
I have been trying this: select * from tableA where exists(select * from TableB)
All this does is give me everything from tableA. Say table B was 2000 rows , I would like to see TableA with 2000 records less.
Any help would be appreciated,
thanks,
Paul
July 8, 2005 at 6:33 am
Look up EXISTS() in BOL. U'll see where u r wrong.
July 8, 2005 at 6:37 am
So you actually want to DELETE the data from table A that's already in tableb?
If so
delete from TableA
where tableA.ID in (select ID from tableB)
should do the trick. Obviously, I'm assuming that ID is a unique key on both tables.
Or to select from table A what's not in table B:
select * from tableA
where tableA.id not in (select id from tableB)
Regards
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 18, 2005 at 12:10 pm
Phil's reply looks good. I'd advise setting up some sample data in test tables before trying it on the live tables.
July 18, 2005 at 4:03 pm
to answer your original question.
Select *
from tablebA
where exists (select *
from tableb
Where Tablea.id = tableb.id)
You need a where clause indicating what criteria you need to establish what data is in both tables, I'm assuming an id column of some sort.
July 25, 2005 at 6:57 am
This would also do the same
Delete from A
Where KeyColumn In
(
Select KeyColumn from B
Union
Select KeyColumn from C
)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply