Geek Answers Handbook

Pl / sql DELETE deletes all rows instead of selected rows

I have a 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 matches where in delete , it deletes the table WHOLE port ! I thought my SQL was right, but obviously it is not, and I do not see what is wrong with it. Can anyone see the problem that causes this to do this?

When update matches, everything works as expected.

Table structure: port connections to the device, jacket and port

+4
source share
2 answers

Your DELETE references the PORT table twice. To clarify, 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);

Note that the subquery is not correlated with p1 . In other words, the result of this subquery will be identical for each row in PORT that is considered for deletion. This way, you are either going to delete all lines, or there are no lines.

(It's also weird that you use LEFT JOIN when you have a non-join predicate on an external table. But in the worst case, it is a performance issue and most likely just confuses anyone reading your code.)

I believe you want:

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

And UPDATE seems to have the same problem; even if it is currently producing the expected results, I’m sure it’s just luck because of the data you are testing. I think this can be simplified:

 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 the EXISTS statement doesn't care if any columns are returned by its subquery; Are the rows just returned at all?

+8
source

Your DELETE deletes everything when the fkjacket field matches: old.pkid, because you did not limit the deletion to anything else. If the EXISTS clause returns a string, 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 lines in the ports table where fkjacket is in the list of fkjacket values ​​returned in the select list.

Are you sure your update is working correctly? It seems you should get the same behavior with it - all rows are updated.

EDIT:

Since your update does not work the same, 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 ports table where fkjacket is in the list of fkjacket values ​​returned in the select list.

+5
source

More articles:


All Articles