求sql语句或存储过程,批量删除同一表中id关联的记录!
id classcode                   parentcode

1 001 0
2 001001 001
3 001001001                   001001
4 001001001001 001001001
5 001001001001001 001001001001
6 001001001001001001                   001001001001001
7 001001001001001001001 001001001001001001
8 001001001001001001002 001001001001001001
9 001001001002 001001001
10 001001001003 001001001
11 001001001004 001001001
12 001001001005 001001001
13 001001001006 001001001
14 001001002                   001001
15 001001003                   001001

表结构如上

如我想删除id=1的记录,同时也级联删除父编码是001的记录,以此类推,就是把001的所有子记录都删除,该怎么写sql语句呀?? 存储过程也好??

谢谢^

------解决方案--------------------
delete a
from tablename a,tablename b
where b.id=1
and a.classcode like b.classcode+ '% '

------解决方案--------------------
如果a , b 兩表有外鍵約束的話,
可以這樣增加約束，當刪除a 表的id ,會自動刪除，b表的id的記錄

alter table a
add constraint cons_del foreign key(id) references b(id)on delete cascade
這樣它就可以實現自動級聯刪除

對於，要刪除b表 id 的子記錄
我樣可以寫個觸發器來完成

create trigger tri_del on b
after delete
as
delete b
where idclasscode in (select parentcode from deleted)



------解决方案--------------------
delete a
from tablename a
where a.classcode like a.classcode+ '% '
------解决方案--------------------


delete a
from tablename a,tablename b
where a.id=1
and charindex( ', '+a.idclasscode+ ', ', ', '+b.parentcode+ ', ')> 0