There are two ways of deleting things,
DELETE FROM <table> WHERE PK IN ( ...SET... );
DELETE FROM <table> USING <table2>
WHERE <join condition> AND <filter clause>;
I am deleting from a table where the rows are inter-related (it is a
tree); using the first version with IN, it does not delete all rows;
there is a trigger that rearranges the rows when a row is deleted
(updating references etc.). Disabling this trigger results in correct
behavoir.
However, using the second version (using USING) works as expected with
or without disabling the said triggers.
I cannot quite understand this; Are the contents of the IN query
worked out ONCE per outer query and therefore become invalid when
DELETE comes along and changes items that were part of the set ? (for
e.g.)
-ravi
--
:wq