在SQL数据库中，当从Table_A中删除匹配id时，如何从Table_B中删除记录?

I have two tables Table_A & Table_B where Table_B.IX_Id = Table_A.IX_ID. The records in these tables will look like as following:

我有两个表，表a和表b。IX_Id = Table_A.IX_ID。这些表内的记录如下:

QUERY:

查询:

   select * from Table_A where IX_ID = 4783 
   select * from Table_B where IX_Id = 4783

RESULT:

结果:

Table_A result

IX_ID       IX_ParentID          IX_CreationDate
----------- -------------------- --------------------
4783        0                    2015-01-26 10:23:00

Table_B result    

IX_Id       Row_Id      Document_Id
----------- ----------- -----------
4783        1           101 
4783        1           99
4783        1           94

Now let's say I have deleted a record from Table_A which is 4783 than what I want is, also delete records from Table_B where id is 4783.

现在假设我已经从Table_A中删除了一条记录，它比我想要的要4783，也要从Table_B中删除记录，其中id为4783。

I can get the deleted record from Table_A by following query:

我可以通过以下查询得到Table_A中删除的记录:

QUERY:

查询:

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(IX_ID) from Table_A)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN Table_A missingIds on missingIds.IX_ID = Missing.missnum
WHERE missingIds.IX_ID is NULL  order by 1 desc
OPTION (MAXRECURSION 0);

RESULT:

结果:

missnum
-----------
4783

EXPECTED RESULT:

预期结果:

Check that if missnum exists in Table_B than delete those matching records from Table_B else not. How can I achieve this?

检查表_b中是否存在missnum，而不是从Table_B中删除那些匹配记录。我如何做到这一点?

NOTE: I have so many records deleted from Table_A and I am doing tune up in Table_B where corresponding records are in hundreds.

注意:我从Table_A中删除了很多记录，我正在调试Table_B，对应的记录有数百条。

2 个解决方案

#1

You can use NOT IN to find things that don't match a subquery, which is much simpler and the query optimizer has a better chance of choosing something performant

您可以使用NOT IN来查找与子查询不匹配的东西，这更简单，查询优化器有更好的机会选择性能。

DELETE FROM Table_B
WHERE IX_Id NOT IN (SELECT IX_Id FROM Table_A)

I agree with Will's comment about a foreign key if you want to keep IX_Id in Table_B in step with Table_A

我同意Will对一个外键的注释，如果您想要在Table_B中保持IX_Id与Table_A同步。

#2

Take this:

用这个:

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(IX_ID) from Table_A)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN Table_A missingIds on missingIds.IX_ID = Missing.missnum
WHERE missingIds.IX_ID is NULL  order by 1 desc
OPTION (MAXRECURSION 0);

And change it into this:

把它变成这样

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(IX_ID) from Table_A)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
, Missing1 (missnum)
AS
(
 SELECT missnum
 FROM Missing
 LEFT OUTER JOIN Table_A missingIds on missingIds.IX_ID = Missing.missnum
 WHERE missingIds.IX_ID is NULL
)
DELETE FROM Table_B
WHERE IS_Id IN(SELECT missnum FROM Missing1)
OPTION (MAXRECURSION 0);

#1