求sql语句或存储过程,批量删除同一表中id关联的记录!
idclasscode parentcode
10010
2001001001
3001001001 001001
4001001001001001001001
5001001001001001001001001001
6001001001001001001 001001001001001
7001001001001001001001001001001001001001
8001001001001001001002001001001001001001
9001001001002001001001
10001001001003001001001
11001001001004001001001
12001001001005001001001
13001001001006001001001
14001001002 001001
15001001003 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
