isolatedhowl isolatedhowl -5 years ago 215
SQL Question

Create a stored procedure to delete rows that have no values

Create a stored procedure called sp_del_inactive_cust to delete customers that have no orders. The stored procedure should delete 1 row.

Here is the database I am working with.
database diagram

My immediate thought is that I need to check the customers table against the orders table. If the customer is present in the customers table but not in the orders table, that must mean that they have not created any order ids under their customer id. Then, I must delete the customer if they do not have any orders.

I'm not really sure how to go about scripting this problem. I need help! Please keep it simple as I am a first semester student.

Here's something I've tried starting:

CREATE PROCEDURE sp_del_inactive_cust
SELECT customers.customer_id,
FROM customers
INNER JOIN orders ON customers.customer_id=orders.customer_id
WHERE /* customer_id is found in customers table but not in orders table */

And then I will execute the procedure.

This question has been answered thanks to the help of Michael Fredrickson.

Here are the final statements which deleted the required 1 row:

CREATE PROCEDURE sp_del_inactive_cust
DELETE customers
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;

EXECUTE sp_del_inactive_cust;

Answer Source
CREATE PROCEDURE sp_del_inactive_cust


    customers c
    LEFT OUTER JOIN orders o
        ON C.customer_id = o.customer_id
    o.customer_id IS NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download