日期:2014-05-18  浏览次数:20340 次

同时删除多个表中的数据的问题
我在网上找到一些同时删除多个表中的数据的 语句 
但是总是报错,说“,”那里有错
好象始终都是执行到“,”就会出错
请高人指点

delete A表,b表 from A表,b表 where A表.id=b表.id and A表.id=3  
   
delete from A表,b表 using A表,b表 where A表.id=b表.id and A表.id=3

------解决方案--------------------
用外键关连时,要先删关连表
delete b from A表,b表 where A表.id=b表.id and A表.id=3 

delete a from A表,b表 where A表.id=3 

------解决方案--------------------
使用事务来处理!

create proc deleteSame
as
begin 
begin Transaction
declare @error1 int, @error2 int
declare @indextable table(id int ,names varchar(20))
insert @indextable(id) select id from (select b.* from a inner join b on a.id =b.id) x
begin
delete from a where a.id in(select id from @indextable)
set @error1 = @@error
end
begin
delete from b where b.id in (select id from @indextable)
set @error2 = @@error
end
if(@error1<>0 or @error2<>0)
rollback
else
commit
end

exec deleteSame
------解决方案--------------------
delete A表,b表 --这是错误的,同时指定删两个表
------解决方案--------------------
放到一个存储过程中去执行.

CREATE PROCEDURE myproc
as 
begin
delete from A where ...
delete from B where ...
end