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