I have a table with hierarchical data in it but there is some duplication that I want to remove with a single SQL statement. If a single SQL statement cannot be used, then the most efficient PL/SQL block can also be used.
There are two column in the table as shown below.
Prev_id is the parent of Next_id. All is well till the third line where a simple connect by query can be used but the last two records are the indirect relationships which should not be there and I want to remove them. The table contains millions of rows and I want to use the most efficient method to remove the records. I have tried to use subqueries in DELETE statement but when I run it on the test data, it removes some rows which it should not. Any help will be greatly appreciated.