pl/sql DELETE is deleting all rows instead of selected rows

Question

I have the trigger:

create or replace
TRIGGER JACKET_DELETE 
BEFORE DELETE ON JACKET 
FOR EACH ROW 
BEGIN
  DELETE FROM PORT
  WHERE EXISTS
    (SELECT * FROM port LEFT JOIN device on port.fkdevice = device.pkid
     where port.fkjacket = :old.pkid
     and device.fkdevice_type = 1);

  UPDATE PORT
  set port.fkjacket = null, port.fkport = null
  WHERE EXISTS
(SELECT port.fkjacket, port.fkport FROM port LEFT JOIN device on port.fkdevice = device.pkid
     where port.fkjacket = :old.pkid
     and device.fkdevice_type <> 1);
END;

For some reason, when the where in the delete matches, it deletes the WHOLE port table! I thought my SQL was correct, but obviously it's not, and I can't see what's wrong with it. Can anyone see the issue that is making it do this?

When the update matches, everything works as expected.

---

table structure: port links to device, jacket, and port

Answer 1

Your DELETE is referencing the PORT table twice. To clarify, let's first modify the statement to include table aliases:

  DELETE FROM PORT p1
  WHERE EXISTS
    (SELECT * FROM port p2 LEFT JOIN device on p2.fkdevice = device.pkid
     where p2.fkjacket = :old.pkid
     and device.fkdevice_type = 1);

Notice that the subquery is not correlated to p1. In other words, the result of this subquery will be identical for every row in PORT that is being considered for deletion. So you're either going to delete all rows or no rows.

(It's also odd that you use a LEFT JOIN when you have a non-join predicate on the outer table. But that's at worst an efficiency problem and more likely just confusing to anyone reading your code.)

I believe that what you want is:

DELETE FROM PORT
WHERE fkjacket = :old.pkid
AND EXISTS
  (SELECT NULL FROM device
   WHERE device.pkid = port.fkdevice
     AND device.fkdevice_type=1);

And the UPDATE seems to have the same issue; even if it's currently giving you expected results, I bet that's just luck due to the data you're testing with. I think it can be simplified to:

UPDATE PORT
  set port.fkjacket = null, port.fkport = null
  WHERE port.fkjacket = :old.pkid
    AND EXISTS
    (SELECT NULL FROM device
       WHERE port.fkdevice = device.pkid
       AND device.fkdevice_type <> 1);

Note that an EXISTS operator doesn't care what if any columns are returned by its subquery; just whether rows are returned at all.

Answer 2

Your DELETE is deleting everything when the fkjacket field matches :old.pkid, because you haven't restricted the delete on anything else. If the EXISTS clause returns a row, then everything goes.

Change this to something like:

  DELETE FROM PORT
  WHERE fkjacket IN
    (SELECT port.fkjacket FROM port LEFT JOIN device on port.fkdevice = device.pkid
     where port.fkjacket = :old.pkid
     and device.fkdevice_type = 1);

This will delete all rows in the port table where fkjacket is in the list of fkjacket values returned in the select list.

Are you sure your update is working correctly? Seems to me you should be getting the same sort of behavior with it - all rows updated.

EDIT:

Since your update is failing the same way, I suggest changing it to:

  UPDATE PORT
  SET port.fkjacket = null, port.fkport = null
  WHERE fkjacket IN
       (SELECT port.fkjacket 
          FROM port LEFT JOIN device on port.fkdevice = device.pkid
         WHERE port.fkjacket = :old.pkid
           AND device.fkdevice_type <> 1);

This will update all rows in the port table where fkjacket is in the list of fkjacket values returned in the select list.