SQL Server：使用一个表中的信息删除另一个行中的行

Question

我在编写一些SQL Server脚本以从两个数据库中删除几行时遇到了一些困难。我已经看到关于该主题的其他几个问题，但似乎没有任何工作适用于这种情况。我提前为这样一个基本问题表示道歉，但这是一个无法弥补的问题。我查询了很多数据，但很少写表格。

我有两个表rollinfo和defects它们由

defects.roll_id = rollinfo.roll_idx 

挂有一个在rollinfo表中的列称为rollinfo.num_defects。

如果该值大于@MAX_DEFECTS，我想将其删除。另外我想从defects表中删除任何相应的行。

选择这个信息很简单：

SELECT 
    D.ROLL_ID, 
    R.ROLL_IDX 
FROM 
    VISION17SLITTER.DBO.ROLLINFO R 
INNER JOIN 
    VISION17SLITTER.DBO.DEFECTS D ON D.ROLL_ID = R.ROLL_IDX 
WHERE 
    R.NUM_DEFECTS > @MAX_DEFECTS 

但是我不知道是否行可以从两个表中一次通过只是改变了select语句删除或者如果我需要做某种类型的删除“哪里存在”声明。

为了记录，这将是约20万行，所以如果有多种方法来做到这一点，我想知道哪个更有效。

感谢，

丹

Answer 1

由于需要从2个表删除，则需要有两个DELETE报表，所以我认为最好的办法是把你需要删除的ID写入临时表中d，然后用它来驱动你的两个DELETE声明：

-- create the temp table - you didn't mention what the datatype for these two columns is - adapt as needed 
CREATE TABLE #IDsToBeDeleted (ID INT NOT NULL) 

-- select those ID's you want to delete into the temp table 
INSERT INTO #IDsToBeDeleted (ID) 
    SELECT 
     D.ROLL_ID 
    FROM 
     VISION17SLITTER.DBO.ROLLINFO R 
    INNER JOIN 
     VISION17SLITTER.DBO.DEFECTS D ON D.ROLL_ID = R.ROLL_IDX 
    WHERE 
     R.NUM_DEFECTS > @MAX_DEFECTS 

-- based on the temp table, now delete from the two tables 
-- again, from your question it isn't entirely clear which 
-- is the "parent" table, and which the "child" table - so you 
-- might need to change the order of deleting those rows to 
-- match your situation 

BEGIN TRANSACTION 
BEGIN TRY  
    DELETE FROM VISION17SLITTER.DBO.DEFECTS 
    WHERE ROLL_ID IN (SELECT ID FROM #IDsToBeDeleted)  

    DELETE FROM VISION17SLITTER.DBO.ROLLINFO 
    WHERE ROLL_IDX IN (SELECT ID FROM #IDsToBeDeleted) 

    COMMIT TRANSACTION 
END TRY 
BEGIN CATCH 
    -- report and log the error 
    ROLLBACK TRANSACTION 
END CATCH 

Answer 2

你试过吗？

这可能会也可能不会工作，具体取决于您的数据库。否则，在同一批次中使用两次删除（你说你想写一个脚本，所以一批是正确的事情）。在这个简单的例子，你并不需要一个事务，因为你已经知道你是用数据库交互的唯一的人：

DELETE D 
FROM 
    VISION17SLITTER.DBO.ROLLINFO R 
    INNER JOIN 
    VISION17SLITTER.DBO.DEFECTS D 
    ON D.ROLL_ID = R.ROLL_IDX 
WHERE R.NUM_DEFECTS > @MAX_DEFECTS; 

DELETE R 
FROM 
    VISION17SLITTER.DBO.ROLLINFO R 
WHERE R.NUM_DEFECTS > @MAX_DEFECTS; 

GO 

如果你真的不想要一个脚本来手动启动，同时应用程序看台不过，你必须考虑并发性和实际成交价是为了：

BEGIN TRANSACTION 

    DELETE D 
    FROM 
    VISION17SLITTER.DBO.ROLLINFO R 
    INNER JOIN 
    VISION17SLITTER.DBO.DEFECTS D 
    ON D.ROLL_ID = R.ROLL_IDX 
    WHERE R.NUM_DEFECTS > @MAX_DEFECTS; 

    DELETE R 
    FROM 
    VISION17SLITTER.DBO.ROLLINFO R 
    WHERE R.NUM_DEFECTS > @MAX_DEFECTS; 

COMMIT;