如何在 SQL 服务器中使用 INNERJOIN 从多个表中删除

1. 您总是可以在表的关系上设置级联删除。

2. 您可以在一个存储过程中封装多个删除。

3. 您可以使用事务来确保一个工作单元。

正如 Aaron 已经指出的，您可以将 delete 行为设置为 CASCADE，这将在删除父记录时删除子记录。除非您希望发生某种其他奇迹(在这种情况下，Aaron 回答的第2、3点将是有用的) ，否则我不明白为什么需要使用内部连接删除。

您可以在 SQL Server 中的 DELETE 中的 FROM 子句中使用 JOIN 语法，但仍然只从第一个表中删除，而且它是专有的 Transact-SQL 扩展，可以替代子查询。

以 给你为例:

 -- Transact-SQL extension
DELETE
FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN
Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;

基本上，没有你必须作出三个删除语句在一个事务，孩子先，然后父母。如果这不是一次性的，并且它的存在不会与任何现有的触发器设置冲突，那么设置级联删除是一个好主意。

在 SQL 服务器中，无法使用连接从多个表中删除记录。 因此，在删除表单父表单之前，必须先从子表单中删除。

只是好奇。.在 MySQL 中这真的可能吗？它会删除 t1和 t2吗？或者我只是误解了这个问题。

但是，如果只想删除带有多个连接条件的 table1，那么不要将要删除的表改为别名

这个:

DELETE t1,t2
FROM table1 AS t1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

在 MSSQL 中应该这样编写:

DELETE table1
FROM table1
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

对比另外两个常用的 RDBMS 执行删除操作的方式:

Http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html

您可以利用这个示例中的“已删除”伪表，比如:

begin transaction;


declare @deletedIds table ( id int );


delete from t1
output deleted.id into @deletedIds
from table1 as t1
inner join table2 as t2
on t2.id = t1.id
inner join table3 as t3
on t3.id = t2.id;


delete from t2
from table2 as t2
inner join @deletedIds as d
on d.id = t2.id;


delete from t3
from table3 as t3 ...


commit transaction;

显然你可以做一个输出删除。如果您需要加入第三个表的内容，也可以在第二个 delete 上使用。

作为附注，您也可以做插入。* 插入语句，并且两者都插入。然后删除 * 。更新声明 * 。

编辑: 另外，您是否考虑过在表1上添加一个触发器，以便从表2 + 3中删除？您将处于一个隐式事务的内部，并且还可以使用“ insert. 和“删除”。”伪表。

都已经指出来了。只需在父 table上使用 DELETE ON CASCADE，或者从 child-table中删除，然后从 parent中删除。

从主表中删除一些记录以及从两个详细表中删除相应记录的示例:

BEGIN TRAN


-- create temporary table for deleted IDs
CREATE TABLE #DeleteIds (
Id INT NOT NULL PRIMARY KEY
)


-- save IDs of master table records (you want to delete) to temporary table
INSERT INTO #DeleteIds(Id)
SELECT DISTINCT mt.MasterTableId
FROM MasterTable mt
INNER JOIN ...
WHERE ...


-- delete from first detail table using join syntax
DELETE d
FROM DetailTable_1 D
INNER JOIN #DeleteIds X
ON D.MasterTableId = X.Id




-- delete from second detail table using IN clause
DELETE FROM DetailTable_2
WHERE MasterTableId IN (
SELECT X.Id
FROM #DeleteIds X
)




-- and finally delete from master table
DELETE d
FROM MasterTable D
INNER JOIN #DeleteIds X
ON D.MasterTableId = X.Id


-- do not forget to drop the temp table
DROP TABLE #DeleteIds


COMMIT

这是另一种删除记录而不留下孤儿的方法。

Declare @user Table(keyValue int  , someString varchar(10))
insert into @user
values(1,'1 value')


insert into @user
values(2,'2 value')


insert into @user
values(3,'3 value')


Declare @password Table(  keyValue int , details varchar(10))
insert into @password
values(1,'1 Password')
insert into @password
values(2,'2 Password')
insert into @password
values(3,'3 Password')


--before deletion
select * from @password a inner join @user b
on a.keyvalue = b.keyvalue
select * into #deletedID from @user where keyvalue=1 -- this works like the output example
delete  @user where keyvalue =1
delete @password where keyvalue in (select keyvalue from #deletedid)


--After deletion--
select * from @password a inner join @user b
on a.keyvalue = b.keyvalue

DELETE     TABLE1 LIN
FROM TABLE1 LIN
INNER JOIN TABLE2 LCS ON  CONDITION
WHERE CONDITION

$sql = “ DELETE FROM basic_tbl，education_tbl, personal_tbl，address_tbl，department_tbl 使用 basic_tbl education_tbl, personal_tbl，address_tbl，department_tbl 哪里 b_id = e_id = p_id = a_id = d_id =’”. $id "; $rs = mysqli _ query ($con，$sql) ;

在 John Gibb 的答案的基础上，删除两个具有 FK 关系的表中的一组数据:

--*** To delete from tblMain which JOINs to (has a FK of) tblReferredTo's PK
--       i.e.  ON tblMain.Refer_FK = tblReferredTo.ID
--*** !!! If you're CERTAIN that no other rows anywhere also refer to the
--      specific rows in tblReferredTo !!!
BEGIN TRAN;


--*** Keep the ID's from tblReferredTo when we DELETE from tblMain
DECLARE @tblDeletedRefs TABLE ( ID INT );
--*** DELETE from the referring table first
DELETE FROM tblMain
OUTPUT DELETED.Refer_FK INTO @tblDeletedRefs  -- doesn't matter that this isn't DISTINCT, the following DELETE still works.
WHERE ..... -- be careful if filtering, what if other rows
--   in tblMain (or elsewhere) also point to the tblReferredTo rows?


--*** Now we can remove the referred to rows, even though tblMain no longer refers to them.
DELETE tblReferredTo
FROM   tblReferredTo INNER JOIN @tblDeletedRefs Removed
ON tblReferredTo.ID = Removed.ID;


COMMIT TRAN;