September 14, 2009 at 7:57 am
Hello Friends,
I have two table(say table A and table B) variables, both contains records.
I want to delete records of table A that are not in table B.
How can i achieve it?
Thanks.
September 14, 2009 at 8:59 am
amodi (9/14/2009)
Hello Friends,I have two table(say table A and table B) variables, both contains records.
I want to delete records of table A that are not in table B.
How can i achieve it?
Thanks.
Do they contain identical records, are the tables identical in structure?
Have a look at 'EXCEPT' operator if it is going to be of some help to you. Thanks.
---------------------------------------------------------------------------------
September 14, 2009 at 9:08 am
Pleas read the article that is on my signature to learn how to ask questions in a way that will get you a better and faster answer.
As for your question – you can use not exists query or an outer join query. Here are 2 examples:
declare @a table (i int)
declare @b-2 table (i int)
--Inserting data into both tables
insert into @a (i)
select 1
union select 2
union select 3
union select 4
insert into @b-2
select 2
union select 4
--Check the data in the tables
select * from @a
select * from @b-2
--First way is to simply use not in
delete @a
where i not in (select i from @b-2)
--check the results
select * from @a
--Inserting the data into the table again
insert into @a (i)
select 1
union select 3
--Check the data in the table @a
select * from @a
--using delete with left outer join
delete a
from @a as a left join @b-2 as b on a.i = b.i
where b.i is null
--check the results
select * from @a
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply