Delete from sets - 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 2005 Forums

Transact-SQL (2005)

Delete from sets

Author

Topic

clement.store
Starting Member

25 Posts

Posted - 2011-05-13 : 04:51:29

Hello there,

I have two tables A and B. I Want to remove the rows in B that doesnt exist in A. Hence I have tried the following but it seems like there's some issues with the syntax. I looked all over and didnt seem to figure it out yet. Would u guys have any idea?

Delete from B except select * from A

Cheers
Clement.store

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-13 : 04:52:58

[code]
DELETE B
FROM B b
WHERE NOT EXISTS (SELECT * FROM A a WHERE a.ID = b.ID)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

clement.store
Starting Member

25 Posts

Posted - 2011-05-13 : 05:30:31

Thanks. Can this be done using intersect/except...? Guess this can be a little faster using sets...?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-13 : 06:41:00

Just modified Khtan logic
I don't know whether this will affect performance if any one no then post here

Create Table #a (id int,Fname nvarchar(23))
insert into #a
Select 1,'Hi' Union All
Select 2,'Hi'

Create Table #b (id int,Fname nvarchar(23))
Insert into #b
Select 3,'Think' Union all
select 1,'Hi'

--First Try this
;With CTE AS(
Select * from #b except Select * From #a)
Delete From #B where id in (Select id from cte)

--Next Try this
--;with CTE As(
--select * from #b except Select * from #a)
--Delete #B From #B b where exists (Select * from CTE where id=b.id)

----Next Try This
--;With CTE AS(
--Select * from #b intersect Select * From #a)
--Delete #B From #B b Where not exists (Select * from Cte Where id=b.id)

Select * from #A
Select * from #B

Drop table #A
Drop Table #B

In Love... With Me!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-13 : 07:28:30

quote:
Thanks. Can this be done using intersect/except...? Guess this can be a little faster using sets...?

Khtan's code is set-based, and almost certainly the fastest method. Most variations will either generate the same execution plan, or similar enough that performance is equal.

clement.store
Starting Member

25 Posts

Posted - 2011-05-13 : 22:31:01

Thanks Guys, trying it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources