sqlserver-使用select query删除记录

Question

我想删除以下查询返回的所有记录：

    SELECT *
    FROM [Customer] C
    INNER JOIN [BillingStatus] BS 
    ON BS.CustomerID = C.CustomerID 
    INNER JOIN [TCDetails] TC 
    ON TC.CustomerID = BS.CustomerID    
    INNER JOIN [BillingDetails] BD 
    ON BS.CustomerID = BD.CustomerID
    INNER JOIN [AgencyDetails] AD
    ON BD.CustomerID = AD.CustomerID
    INNER JOIN [CustomerDetails] CD
    ON CD.CustomerID = AD.CustomerID
    WHERE C.CYC = 27    

Answer 1

您想要从所有表中删除，因此首先向某个临时表声明客户ID可能会更简单：

SELECT CustomerID 
INTO #TempCustomer 
FROM Customer
WHERE CYC = 27

然后，您需要从以下每个表中删除：

DELETE FROM Customer WHERE CustomerID IN (SELECT CustomerID FROM #TempCustomer)
DELETE FROM BillingStatus WHERE CustomerID IN (SELECT CustomerID FROM #TempCustomer)
DELETE FROM TCDetails WHERE CustomerID IN (SELECT CustomerID FROM #TempCustomer)
DELETE FROM BillingDetails WHERE CustomerID IN (SELECT CustomerID FROM #TempCustomer)
DELETE FROM AgencyDetails WHERE CustomerID IN (SELECT CustomerID FROM #TempCustomer)
DELETE FROM CustomerDetails WHERE CustomerID IN (SELECT CustomerID FROM #TempCustomer)

最后，为了在您之后进行清理，您还应该删除您创建的临时表：

DROP TABLE #TempCustomer

Answer 2

从您的注释中，您希望删除Customer及其在其他表中的所有关系，因此，首先将外键上的Cascade Delete从Customer设置为其他表，然后使用此查询的Delete客户将删除所有其他表

DELETE FROM [Customer] WHERE CYC = 27