Question

sql多表连接删除问题

delete from lm_company as a,lm_Yuzhan as b,lm_Bookinfo as c,lm_message as d,lm_newsinfo as e where a.comid=b.yuzhan_comid and b.yuzhan_id=c.Bkinfo_yuzhanid and c.Bkinfo_id=d.message_bookid and a.comid=e.news_comid and comid=" + Convert.ToInt32(lblid.Text) + "

这一共是5个表，都是有关联的，都是与公司向关联的，现在我要删除公司的信息，当然与公司有关的拍卖会信息，图书信息，评论信息等等都要同时删除，我写的sql语句如上，但是不能被执行，我用的是sql2005，请大家帮帮忙，如果帮我解决了问题还将加分！

Answer 1

原则上,同意youyuan1688和ytbelwxg的方法.
5个表,如果每个表都和lm_company表的comid字段有直接主外键关系.那么ytbelwxg的方法是最佳方案;
仔细看了你的连接条件,猜想,并不是每个表都和lm_company表的comid字段有直接主外键关系.所以通过主外键级联删除的方案就不可行,那么就必须逐个表来删除,而且删除的顺序也有讲究,先删子表,再删主表.

本例中,删除的顺序应该是e,d,c,b,a;
--1.删除e;
sql1="delete from lm_newsinfo
from lm_newsinfo as e,
lm_company as a
where a.comid=e.news_comid
and a.comid=" + lblid.Text +";"

--2.删除d;
sql2="delete from lm_message
from lm_company as a
,lm_Yuzhan as b
,lm_Bookinfo as c
,lm_message as d
where a.comid=b.yuzhan_comid
and b.yuzhan_id=c.Bkinfo_yuzhanid
and c.Bkinfo_id=d.message_bookid
and a.comid=" + lblid.Text +";"

--删除c;
sql3="delete from lm_Bookinfo
from lm_company as a
,lm_Yuzhan as b
,lm_Bookinfo as c
where a.comid=b.yuzhan_comid
and b.yuzhan_id=c.Bkinfo_yuzhanid
and a.comid=" + lblid.Text + ";"

--删除b;
sql4="delete from lm_Yuzhan
where yuzhan_comid=" + lblid.Text + ";"

--删除a;
sql5="delete from lm_company
where comid=" + lblid.Text + ";"

--合并sql1,sql2,sql3,sql4,sql5
sql=sql1+sql2+sql3+sql4+sql5

--在你的客户端执行sql命令串即可

Answer 2

应该单个表删除,先删除从表信息,最后删除主表信息.
先删d表,
delete lm_message from lm_company as a,lm_Yuzhan as b,lm_Bookinfo as c,lm_message as d where a.comid=b.yuzhan_comid and b.yuzhan_id=c.Bkinfo_yuzhanid and c.Bkinfo_id=d.message_bookid and comid=" + Convert.ToInt32(lblid.Text) + "
再c表,
delete lm_Bookinfo from lm_company as a,lm_Yuzhan as b,lm_Bookinfo as c where a.comid=b.yuzhan_comid and b.yuzhan_id=c.Bkinfo_yuzhanid and comid=" + Convert.ToInt32(lblid.Text) + "
再b表和e表,
delete lm_Yuzhan from lm_company as a,lm_Yuzhan as b where a.comid=b.yuzhan_comid and comid=" + Convert.ToInt32(lblid.Text) + "

delete lm_newsinfo from lm_company as a,lm_newsinfo as e where a.comid=e.news_comid and comid=" + Convert.ToInt32(lblid.Text) + "
最后是a表
delete from lm_company where comid=" + Convert.ToInt32(lblid.Text) + "

Answer 3

那么你应该逐个表用delete删除；如果你设置了外键并且设置外键属性为主删除时从表也删除的话，那么只要删除公司信息，其它表中相应信息会一起删除的，否则你应该逐个表用delete删除

Answer 4

如果有FK，而且在创建table时，指定了delete cascade的话，就可以直接删除

Answer 5

把这些表中的comid分别作为lm_company表comid的外键添加级联删除功能可以了