FYI: 10g DELETE does not match SELECT START WITH CONNECT BY AND root

No time to mess with this, I don't need a response ... jest reporting what I found in case someone else needs it.

This particular problem involves the use of a somewhat recursive table
... not truly recursive (the parent key is within the table which
specifies the child key as its primary key). In my case, children may have multiple parents ... as such, an *orphan* simply does not exist in my *structure* table as a child.

In my case, in order to DELETE related rows from yet another table for both the *root*, as well as all its children, I used:

WHERE other_id = ? AND (inst_id = ? OR inst_id IN (SELECT child_id FROM structure_table START WITH parent_id = ? CONNECT BY PRIOR child_id = parent_id))

When I substitute DELETE with SELECT * FROM, it returns exactly the rows I expect. But the DELETE simply indicates it deleted 0 rows ... on 10g. On an identical 7.3 server, the DELETE deletes the same rows the SELECT returns ... as expected.

IOW, this is inconsistent from 7.3 to 10g.

I have resolved this particular situation for myself another way ... mostly due to the nature of my data which afforded that opportunity.

I have done no other investigation as to whether our 10g is the latest
... hang on ... it says 10.2.0.20.0 (64-bit) ... FWIW.

If anyone knows of a specific patch to correct this, I would be interested in that. Otherwise, I don't care to waste any more time on this than I already have.

Thanks! Received on Mon Jan 15 2007 - 12:53:00 CST