删除记录时"Subquery returned more than 1 value"，如果我更改获取的数字，则不是

Question

我试图从4个数据库中删除数百万条记录，但遇到了一个意外错误。我制作了一个临时表，其中包含我希望删除的所有id的列表：

CREATE TABLE #CaseList (case_id int)
INSERT INTO #CaseList 
SELECT DISTINCT id 
FROM my_table
WHERE <my criteria for choosing cases>

我已经删除了所有相关的记录(在case_id上有外键(

DELETE FROM image WHERE case_id in (SELECT case_id from #CaseList)

然后，我批量从my_table中删除记录(以免破坏事务日志——尽管我的数据库处于简单模式——但在进行删除等更改时，事务日志仍然会增长(：

DELETE FROM my_table WHERE id in (SELECT case_id
FROM #CaseList
ORDER by case_id
OFFSET 0 ROWS
FETCH NEXT 10000 ROWS ONLY)

这将适用于一轮、三轮或五轮(所以我已经删除了10k-50k记录(，然后将失败，并显示以下错误消息：

消息512，级别16，状态1，过程trgd_image，第188行子查询返回了多个值。当子查询跟在=、！=、<lt；=，>，>=或者当子查询用作表达式时

这真的很奇怪，因为正如我所说，我已经从图像表中删除了所有相关的记录。然后它变得更奇怪，因为如果我选择较小的批次，删除操作不会出错。

我通常将FETCH NEXT减半(5k(，然后再次减半(2500(，然后再减半(1200(等，直到它工作

DELETE FROM my_table WHERE id in (SELECT case_id
FROM #CaseList
ORDER by case_id
OFFSET 50000 ROWS
FETCH NEXT 1200 ROWS ONLY)

然后重复这个数量，直到我通过它失败的地方，然后将其恢复到10000，它将在一批或三批中再次工作。。。

DELETE FROM my_table WHERE id in (SELECT case_id
FROM #CaseList
ORDER by case_id
OFFSET 60000 ROWS
FETCH NEXT 10000 ROWS ONLY)

然后以同样的错误再次失败。。。漂洗，洗涤，然后重复。

当图像表中存在NOT相关记录时，是什么导致子查询错误？为什么选择小批量的病例会"绕过它"，然后再次允许大批量？

我真的很想找到一个解决方案，这样我就可以制作一个WHILE循环，以这种方式在数百万行中运行删除操作，而不必手动管理它，这将花费我数周的时间，从4个数据库中删除数百万行。

Answer 1

您显示的查询不会产生您看到的错误。如果你确定是这样，你就有一个错误报告。我的猜测是trgd_image，第188行(或附近的某个地方(，您会发现您使用的是标量比较，=而不是in。

我也有一些建议给你，免费咨询。我写了很多像你这样的查询，从来没有使用过OFFSET 60000 ROWS FETCH NEXT 10000 ROWS ONLY这样的查询。您也不需要，如果不需要，您的SQL将更容易编写。

首先，除非你的机器在2018年的数据规模上严重不足，否则我认为你会发现10万行交易是可以的。如果没有，至少试着去理解为什么没有。一台管理数百万行的机器应该能够毫不费力地处理其中的1%。

当填充#CaseList时，陷阱@@rowcount。然后你可以打印/记录它，并计算你的作品中"块"的数量。

不过，理想情况下，没有临时桌子。相反，这些案例可能有一些你可以操作的逻辑分组。它们可能有地区、所有者或日期，无论最初用来选择它们的是什么。对此进行迭代，例如

delete from T where id in (select id from S where user = 1

一旦你做到了，你就可以写一个循环：

select @user = min(user) from S where ...
while @user is not NULL begin
print "deleting cases for user", @user
delete from T where id in (select id from S where user = @user)
select @u = @user
select @user = min(user) from S where ... and user > @u
end

这样，如果这个过程中途失败了——因为任何的原因——你就有了一个逻辑删除分组和一个干净的中断：你知道所有小于@user的用户(或其他(的情况都被删除了，你可以查看"当前"的情况有什么问题。很多时候，你会发现这个问题并不是唯一的，通过解决它，你可以防止未来与他人发生问题。