如何在SQL Server中使用单个SQL查询删除多个表中的数据 [英] How to delete data fom multiple tables using single SQL query in SQL server

问题描述

我有五个表，如果我删除gridview中的记录，我需要删除所有使用sql的记录，我该怎么做呢。在这五个表中，tbl4是主表，tbl4中的id传递给所有其余的四个表，如果我删除gridview中的记录，所有与id 123相关的数据应该在剩下的四个表中删除我该怎么办呢

I have five tables and if i delete a record in gridview i need to delete all the records rleated to that using sql how can i do this.In these five tables tbl4 is the main table and that id in tbl4 is passed to all the remaining four tables and if i delete a record in gridview all the data related to id 123 should be deleted in remaining four tables how can i do this

select  * from Tbl1 where PName='123'
select  * from Tbl2 where PName='123'
select  * from Tbl3 where PName='123'
select  * from Tbl4 where ID='123'
select  * from Tbl5 where PName='123'

我尝试了什么：



i无法获得如何实现这个

What I have tried:

i could not get how to achieve this

推荐答案

由于删除语句只能在SQL中一次从一个表中删除，因此必须使用存储过程一次从多个表中删除。



但是，在表 tbl1 ，<$ c $中看起来好像是 Pname c> tbl2 ， tbl3 和 tbl5 是<$ c $上Pname的外键C> tbl4 。如果是这种情况，那么你想进行级联删除 - 这里有一篇关于CodeProject主题的文章带有示例的SQL Server中的CASCADE [ ^ ]

...以及来自不同来源的另一个使用外键的DELETE CASCADE选项 [ ^ ]

As a delete statement can only delete from one table at a time in SQL, you have to use a stored procedure to delete from multiple tables "at once".

However, it looks as if Pname in tables tbl1, tbl2, tbl3 and tbl5 are foreign keys to Pname on tbl4. If that is the case then you want to do a "cascade delete" - here is one article on the subject in CodeProject CASCADE in SQL Server with Example[^]
...and another from a different source Using DELETE CASCADE Option for Foreign Keys[^]

创建数据库存储过程并调用以下查询，当您从gridview中删除时，将id传递给存储过程

类似：

创建过程p_Delete_records

（id int）

as

开始

DECLARE @ID INT = 123;

从Tbl1删除，其中PName = @ ID;

从Tbl2删除，其中PName = @ID;

从Tbl3删除，其中PName = @ ID;

从Tbl5删除，其中PName = @ ID;

从Tbl4删除，其中ID = @ID ;

结束

Hi,
Create a Database store procedure and call the below query and when you are deleting from the gridview pass the id to store procdeure
Like:
Create Procedure p_Delete_records
(id int)
as
begin
DECLARE @ID INT =123;
DELETE from Tbl1 where PName=@ID;
DELETE from Tbl2 where PName=@ID;
DELETE from Tbl3 where PName=@ID;
DELETE from Tbl5 where PName=@ID;
DELETE from Tbl4 where ID=@ID;
end

这篇关于如何在SQL Server中使用单个SQL查询删除多个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！