求关联删除语句。谢谢

[idiotnoarea]

id sort parentid
1 a 0
2 b 0
3 c 2
4 d 1
5 e 3
6 f 5


现在要求删除2则能够一并删除3，5，6
谢谢
程序我可以写出来。但是用sql就写不出来，
请赐教

[idiotnoarea]

正在消化
谢谢参与

[CrazyFor]

用级联触发器可以做，

也可参考 ：

http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.8570978

[wuqiuzhi]

这样吧：
使用一个触发器，里面这样做：
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<trigger_name, sysname, trig_test>'
AND type = 'TR')
DROP TRIGGER <trigger_name, sysname, trig_test>
GO

CREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_name, sysname, pubs.dbo.sales>
FOR DELETE
AS
BEGIN
declare @F int,@Id1 int,@Id2 int
select @Id1=id from deleted
select @Id2=id from table_name where parentid=@Id1
set @F=case when @Id2 is null then 0 else 1 end
while @F=1
begin
set @Id1=Id2
deleted from table_name where id=@Id2
select @Id2=id from table_name where parentid=@Id1
set @F=case when @Id2 is null then 0 else 1 end
end
END
GO

[caiyunxia]

if exists(select * from table where id=2)
delete from table where id in(2,3,5,6)

[idiotnoarea]

呵呵。关键是我怎么得到这个2,3,5,6那？
我现在需要得是这个啊
谢谢参与

[happydreamer]

declare @temp table (id char(8))

insert @temp values ('2')

while exists (
select id from table
where parentid in (select id from @temp)
and id not in (select id from @temp)
)
insert @temp
select id from table
where parentid in (select id from @temp)
and id not in (select id from @temp)
---把所有子节点插入@temp

delete from table tempTab
where parentid in (select id from @temp)