CODEWITHSUNDEEP
sql-server
TheGeneral
TheGeneral

Reputation: 81553

SQL delete parent records with no child records

I'm trying to delete all CombinableOrders which have no Orders associated with them

My attempt

DELETE 
 FROM CombinableOrders pr    
 WHERE NOT EXISTS(SELECT * FROM Orders cr WHERE pr.Id = cr.CombinableOrder_Id)

Sql server error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I know this must be simple

Update

I think the pr and cr were causing the problem and sql was giving me an error for a previous statement

Upvotes: 1

Views: 3468

Answers (2)

Sidaoui Majdi
Sidaoui Majdi

Reputation: 419

Try this:

DELETE 
 FROM CombinableOrders pr    
 WHERE ((SELECT COUNT(*) FROM Orders cr WHERE pr.Id = cr.CombinableOrder_Id) = 0)

Upvotes: 0

Nugsson
Nugsson

Reputation: 324

Try this:

DELETE
    CombinableOrders 
FROM 
    CombinableOrders
WHERE 
    NOT EXISTS(SELECT 1 FROM Orders WHERE CombinableOrders.Id = Orders.CombinableOrder_Id)

Upvotes: 3

Related Questions