April 28, 2015 at 4:01 pm
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))
April 28, 2015 at 4:45 pm
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);
April 28, 2015 at 5:00 pm
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))
April 28, 2015 at 6:18 pm
mistylove98 (4/28/2015)
I tried this lynn to no avail. I dont understanddelete 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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply