CODEWITHSUNDEEP
sql
minimay
minimay

Reputation: 45

Delete statement with a subquery

I have to write a delete statement for the customer table. delete customers that have not put in any orders. use a subquery and the exist operator.

Im having trouble with the proper way this query should be displayed this is what i tested and had no luck with. Can anyone tell me how to fix this statement?

         delete customers from  dbo.customers
        WHERE        (customerID NOT exist
       (SELECT  customerID
                           FROM            dbo.Orders
                           where customerid = ordersid))

Upvotes: 2

Views: 530

Answers (5)

Bohemian
Bohemian

Reputation: 425358

Your syntax is off, but so is your logic - you don't need a correlated subquery (ie one that runs for every row, because it uses values from the row). Instead, just do this:

delete from customers
where customerID not in (select customerID from orders)

Upvotes: 1

Zerk
Zerk

Reputation: 1593

The following link should be of use: http://www.w3schools.com/sql/sql_delete.asp

Though looking at your code, the following statement ought to work.

This is assuming that you have a customers table and an orders table, and customerid is in both tables linking the customer to the order. Each order would have a customerid on it, so if the customerid in the customer table is not found in the sub-query then they have no orders.

delete from dbo.customers t1
where t1.customerID not in
(select distinct customerid from dbo.orders)

Upvotes: 0

Sharn White
Sharn White

Reputation: 624

I'd go with the following. 

DELETE FROM  dbo.customers
WHERE dbo.customers.customer_id not in (
    SELECT dbo.orders.customer_id 
    FROM dbo.orders 
    GROUP BY dbo.orders.customer_id
)

Upvotes: 0

Scott
Scott

Reputation: 370

I think your problem lies in the where clause of your subselect. You're comparing customId to ordersId which you most likely don't want to do. Try simply removing the where clause from the subselect because you want to ensure that the customerId is not in the unfiltered Orders table and instead of not exists do a not in. You will also want to make sure that you have customerID indexed for a statement like this.

delete customers from dbo.customers
where customerID not in (SELECT  customerID FROM dbo.Orders)

Upvotes: 1

Mureinik
Mureinik

Reputation: 312219

Your syntax for using the exist operator is slightly off, although the general idea is in the right direction:

DELETE FROM  dbo.customers
WHERE        NOT EXISTS (SELECT *
                         FROM   dbo.orders
                         WHERE  dbo.customers.customer_id = 
                                dbo.orders.customer_id)

Upvotes: 1

Related Questions