Deleting from self-joined table

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)

Deleting from self-joined table

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-16 : 16:38:51

That's the point: delete using self-join. How?

if object_id('tempdb..#books')>0 drop table #books
create table #books (n int primary key, price int)
insert into #books
select 1, 11 union all
select 2, 11 union all -- should be deleted (i.e., delete duplicate prices)
select 3, 11 union all -- and this
select 4, 22 union all
select 5, 22 union all -- and this
select 6, 33

---- here your delete statement

select * from #books
drop table #books

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 16:43:07

Are you looking for an alternate solution to the delete duplicates method?

http://www.sqlteam.com/item.asp?ItemID=3331

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-16 : 16:50:24

Thanks, Tara. No, not quite so. I'd just would like to see
self-joined deleting. Of course, any derived tables "allowed".

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-16 : 17:38:16

[code]delete
from #books
where n not in
(
select min(n) n
from #books b
join
(
select price
from #books
group by price

) d on d.price = b.price
group by d.price
)[/code]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-16 : 17:42:28

Quite good. And this is mine:

delete from b from #books b right join
(select min(n) n, price from #books group by price having count(*)>1) z
on b.price=z.price and b.n>z.n
where b.n is not null -- note: there ALL b.n are NOT null

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-16 : 17:44:30

And yours is faster....

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-16 : 18:13:29

>faster

It does not matter. The point is the WHERE condition.
There are no nulls in the "left" table. Meanwhile without
this WHERE it will not work. Funnily. I didn't know this fact.

HansVE
Starting Member

2 Posts

Posted - 2004-01-17 : 13:40:27

delete a
from #books a join #books b on b.price = a.price
where a.n > b.n

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-17 : 19:29:54

Very good, Hans. But inner joins can't produce this:

Server: Msg 553, Level 16, State 1, Line 11
DELETE over nullable side of outer join query on table '#books'.

And SQL requires the explicit WHERE .... is not null, even
if there are no any nulls in the nullable side......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources