CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Abhishek Singhal
Abhishek Singhal

Reputation: 57

Delete records with select query

I want to Delete all The Records which the is returned by following query:

    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

Upvotes: 0

Views: 86

Answers (2)

Tomas Pastircak
Tomas Pastircak

Reputation: 2857

You want to delete from all the tables, so it would probably be simpler to first declare the customer IDs to some temporary table:

SELECT CustomerID 
INTO #TempCustomer 
FROM Customer
WHERE CYC = 27

Then, you'll need to delete from each of these tables:

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)

At the end, to clean up after you, you should drop the temporary table you created as well:

DROP TABLE #TempCustomer

Upvotes: 2

Reza
Reza

Reputation: 19843

From your comments you want to delete Customer and all it's relations in other tables so, first of all set Cascade Delete on foreign keys from Customer to other Tables, then Delete Customer with this query all other tables will be deleted

DELETE FROM [Customer] WHERE CYC = 27

Upvotes: 0

Related Questions