CODEWITHSUNDEEP
sqlpostgresqlplpgsqlsql-delete
jhj1998
jhj1998

Reputation: 27

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;

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?

Upvotes: 2

Views: 1710

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

Your basic function can work like this:

CREATE OR REPLACE FUNCTION del_cust(_custid int)
  RETURNS int  --③
  LANGUAGE sql AS
$func$
DELETE FROM customer c
WHERE  c.custid = $1
AND    NOT EXISTS ( -- ①
   SELECT FROM "order" o  -- ②
   WHERE  o.status = 'C'  -- meaning "active"?
   AND    o.custid = $1
   );
RETURNING c.custid; -- ③
$func$;

① Avoid NOT IN if you can. It's inefficient and potentially treacherous. Related:

② "order" is a reserved word in SQL. Better chose a legal identifier, or you have to always double-quote.

③ I made it RETURNS int to signify whether the row was actually deleted. The function returns NULL if the DELETE does not go through. Optional addition.

For a trigger implementation, consider this closely related answer:

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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');

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

Upvotes: 1

Related Questions