I have been reading on Oracle hierarchical queries and all of the documentation/examples seem to be focused on SELECTing.
I have a table with parent-child relations and would like to change parents of children. Everything is done on the same table.
- I can select current parents
- I can select new parents
- Old/new parent pairs have a common (otherwise unique) identifier
The data looks something like this:
ID | DISABLED | NAME | PARAM | PARENT 1 | 1 | qwer | 11 | NULL 2 | NULL | qwer | 12 | NULL 3 | NULL | tyui | 3 | 1 4 | NULL | wert | 4 | 1 5 | NULL | erty | 5 | 1 6 | 1 | asdf | 21 | NULL 7 | NULL | asdf | 22 | NULL 8 | NULL | ghjk | 6 | 6 9 | NULL | hjkl | 7 | 6 10 | NULL | jklp | 8 | 6 And expected output (changed IDs: 3,4,5 and 8,9,10):
ID | DISABLED | NAME | PARAM | PARENT 1 | 1 | qwer | 11 | NULL 2 | NULL | qwer | 12 | NULL 3 | NULL | tyui | 3 | 2 4 | NULL | wert | 4 | 2 5 | NULL | erty | 5 | 2 6 | 1 | asdf | 21 | NULL 7 | NULL | asdf | 22 | NULL 8 | NULL | ghjk | 6 | 7 9 | NULL | hjkl | 7 | 7 10 | NULL | jklp | 8 | 7 EXTRA INFO: The data consists of multiple roots, each having multiple children (I'm testing on a table with ~20k records). The data has only 2 levels. Although I am interested in the PARAM value, I cannot simply update that, because part of application references "correct" roots and another part references children pointing to broken roots, so reparenting is the only option.
- I can distinguish roots from children (
PARENT IS NULL) - I can find roots to be changed (
DISABLED IS NOT NULL) - I can find replacement root IDs (
NAMEcolumns are identical)
Parents to be changed are not static (have to be found in database).
To better illustrate this is how replacement pairs can be constructed (not good at this):
SELECT NEW.ID REPLACEMENT, OLD.ID TO_REPLACE FROM HIERARCHICAL OLD LEFT JOIN HIERARCHICAL NEW ON OLD.NAME = NEW.NAME AND NEW.DISABLED IS NULL WHERE OLD.PARENT IS NULL AND OLD.DISABLED IS NOT NULL AND NEW.ID is NOT NULL I would like to update the table so that entry 3 is parented to 2 instead of 1. Of course there are multiple disabled roots so handwriting updates would be a pain.
I cannot find examples to at least get on track with this, so anything would be appreciated.
I can do this with LOOPs, something like:
DECLARE CURSOR c1 IS SELECT OLDPARENT, NEWPARENT FROM <...>; BEGIN FOR item IN c1 LOOP EXECUTE IMMEDIATE 'UPDATE <...>' END LOOP; END; But performance of such query is dreadful.
