如何在SQL Server中删除两个表中的数据?

I have a table TableA, its PK is AId. I have another table TableB, its only column is BId. BId is actually is a subset of AId. Now, we can not use FK (because of this and that reason).

我有一个表格a，它的PK是援助。我有另一个表b，它的唯一一列是BId。投标实际上是援助的一个子集。现在，我们不能使用FK(因为这个和那个原因)。

How can I Delete the TOP 1000 rows in TableB and its related rows (if the same id exists in table A) in table A?

如何删除表b中的前1000行及其相关行(如果表A中存在相同的id) ?

A solution is to SELECT TOP 100) from tableB and save to temp table, then use it to delete the data in table A, then delete rows in tableB.

一个解决方案是从tableB中选择TOP 100)并保存到temp表，然后使用它删除表A中的数据，然后删除表b中的行。

But it should have a more efficient way to do this.

但它应该有更有效的方法。

I also tried:

我也试过:

DELETE TOP (1000) FROM tableA WHERE AId IN (SELECT BId FROM TableB)

But how can I make sure the top 1000 BId got deleted?

但是，我如何确保前1000个出价被删除?

Thanks

谢谢

3 个解决方案

#1

In general, one DELETE statement can change data only in one table. So, in general, you need two separate DELETE statements to delete rows from two tables.

通常，一个DELETE语句只能在一个表中更改数据。因此，通常需要两个单独的DELETE语句来从两个表中删除行。

Technically, you don't have to write BId to a temp table, just read them twice from TableB.

从技术上讲，您不必向临时表写入BId，只需从TableB中读取两次即可。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

DELETE FROM TableA
WHERE TableA.AId IN
(
    SELECT TOP(1000) TableB.BId
    FROM TableB
    ORDER BY TableB.BId
)
;

DELETE FROM TableB
WHERE TableB.BId IN
(
    SELECT TOP(1000) TableB.BId
    FROM TableB
    ORDER BY TableB.BId
)
;

COMMIT TRANSACTION;

You need to make sure that the set of 1000 BId are the same in both queries, which means that you have to use ORDER BY with TOP. Also, you should do something to prevent changes to TableB by another process between two DELETE statements.

您需要确保这两个查询中的1000个投标集是相同的，这意味着您必须使用ORDER with TOP。此外，您应该做一些事情来防止两个DELETE语句之间的另一个进程对TableB的更改。

In the end, taking care of concurrency issues (for example, by setting the transaction isolation level to serializable) may incur more overhead than writing these 1000 Ids into a temp table.

最后，处理并发问题(例如，通过将事务隔离级别设置为serializable)，可能会产生比将这些1000个id写入临时表更多的开销。

On the other hand, you said "BId is actually is a subset of AId". So, TableA is master, TableB is detail.

另一方面，你说“投标实际上是援助的一个子集”。表a是master，表b是detail。

Technically, you can define a Foreign Key with ON DELETE CASCADE option. It is not clear to me from the question why you can't use foreign key constraint.

从技术上讲，您可以在DELETE CASCADE选项中定义一个外键。我不清楚为什么不能使用外键约束。

ALTER TABLE TableB WITH CHECK ADD CONSTRAINT [FK_TableB_TableA] FOREIGN KEY(BId)
REFERENCES TableA(AId) ON DELETE CASCADE
GO

ALTER TABLE TableB CHECK CONSTRAINT [FK_TableB_TableA]
GO

Then you will delete only from TableA and child rows from TableB would be deleted by the foreign key constraint. Whether that would be more efficient than two explicit DELETEs is hard to tell - you need to test yourself on your hardware.

然后，您将只从表a中删除，而表b中的子行将被外键约束删除。这是否比两个显式删除更有效还很难说——您需要在自己的硬件上进行测试。

In this case one explicit DELETE is enough:

在这种情况下，一个明确的删除就足够了:

DELETE FROM TableA
WHERE TableA.AId IN
(
    SELECT TOP(1000) TableB.BId
    FROM TableB
    ORDER BY TableB.BId
)
;

This will take care of concurrency issues as well.

这也会解决并发问题。

#2

One way to go would be to create a table variable, stored the 1000 ID's and reference that in both deletes. Of course I have no idea if they're integers or not, so change the data type if required. There are other ways, as I'm sure others will point out but this is my standard approach if I need consistency.

一种方法是创建一个表变量，存储1000个ID并在两个删除中引用。当然，我不知道它们是否是整数，所以如果需要，可以更改数据类型。还有其他的方法，我相信其他人会指出但这是我的标准方法，如果我需要一致性的话。

DECLARE @keyValues TABLE (keyValue INT);

INSERT INTO @keyValues
SELECT TOP 1000 FROM TABLEA WHERE AID IN (SELECT BID FROM TABLEB);

DELETE FROM TABLEB WHERE BID IN (SELECT keyValue FROM @keyValues);
DELETE FROM TABLEA WHERE AID IN (SELECT keyValue FROM @keyValues);

#3

other possible ways:

其他可能的方法:

1) DELETE TOP (1000) from TableB with Output clause (into temp table), and then Delete from TableA where AID is in that output table.

1)使用Output子句(into temp表)从表b中删除TOP(1000)，然后从表a中删除help所在的表。

或

2) Implement DELETE trigger for TableB, so when any rows are deleted from TableB, they are also deleted from TableA automatically.

2)为TableB实现删除触发器，当从TableB中删除任何行时，也会自动从TableA中删除。

#1