Delete rows depending on values from another table

How to delete rows from my customer table depending on values from another table, let’s say orders?

If the customer has no active orders they should be able to be deleted from the DB along with their rows (done using CASCADE). However, if they have any active orders at all, they can’t be deleted.

I thought about a PLPGSQL function, then using it in a trigger to check, but I’m lost. I have a basic block of SQL shown below of my first idea of deleting the record accordingly. But it doesn’t work properly as it deletes the customer regardless of status, it just needs one cancelled order in this function and not all cancelled.

CREATE OR REPLACE FUNCTION DelCust(int)
RETURNS void AS $body$
    DELETE FROM Customer 
      WHERE CustID IN (
      SELECT CustID
      FROM Order
      WHERE Status = 'C'
      AND CustID = $1
    );
$body$
LANGUAGE SQL;

SELECT * FROM Customer;

​x
 
CREATE OR REPLACE FUNCTION DelCust(int)RETURNS void AS $body$    DELETE FROM Customer       WHERE CustID IN (      SELECT CustID      FROM Order      WHERE Status = 'C'      AND CustID = $1    );$body$LANGUAGE SQL;​SELECT * FROM Customer;​

I have also tried to use a PLPGSQL function returning a trigger then using a trigger to help with the deletion and checks, but I’m lost on it. Any thoughts on how to fix this? Any good sources for further reading?

Answer

You were very close. I suggest you use NOT IN instead of IN:

DELETE FROM Customer 
  WHERE CustID = $1 AND
        CustID NOT IN (SELECT DISTINCT CustID
                         FROM Order
                         WHERE Status = 'A');

 
DELETE FROM Customer   WHERE CustID = $1 AND        CustID NOT IN (SELECT DISTINCT CustID                         FROM Order                         WHERE Status = 'A');​

I’m guessing here that Status = 'A' means “active order”. If it’s something else change the code appropriately.

Advertisement

Answer