How to add a subquery to a delete statement

Question

How to add a subquery to a delete statement

mistylove98

SSC Journeyman

Points: 92
More actions
April 28, 2015 at 4:01 pm

#297539

I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator can someone point me in the right direction? thanks
this is what i tried that im sure is wrong
delete customers from dbo.customers
WHERE (customerID NOT exist
(SELECT customerID
FROM dbo.Orders))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mistylove98 (4/28/2015)
I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator can someone point me in the right direction? thanks

Here is a snippet.

delete from del

from dbo.YourTable del

where not exists(select 1 from dbo.YourOtherTable yot where yot.KeyColumn = del.KeyColumn);

mistylove98 SSC Journeyman Points: 92 More actions · Answer 2

I tried this lynn to no avail. I dont understand

delete customers from dbo.customers

WHERE customerID NOT exists

(SELECT customerID

FROM dbo.LMOrders

where customerid = ordersid))

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

mistylove98 (4/28/2015)
I tried this lynn to no avail. I dont understand
delete customers from dbo.customers
WHERE customerID NOT exists
(SELECT customerID
FROM dbo.LMOrders
where customerid = ordersid))

You need to correlate your subquery to the delete statement.

delete from del

from dbo.customers del

where not exists (select 1 from dbo.LMOrders o where o.CustomerID = del.CustomerID);

The WHERE clause in the subquery correlates it to the parent, in this case, a DELETE statement. You need to check that I have the column names right in the subquery.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions