Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Visit Stack Exchange
Stack Internal
Knowledge at work
Bring the best of human thought and AI automation together at your work.
Explore Stack InternalTo 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 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 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 26 | 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.
PARENT IS NULL)DISABLED IS NOT NULL)NAME columns are identical)Parents to be changed are not static (have to be found in database).
ID | DISABLED | NAME | PARAM | PARENT 1 | 1 | qwer | 1 | NULL 2 | NULL | qwer | 2 | NULL 3 | NULL | tyui | 3 | 1 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.
PARENT IS NULL)DISABLED IS NOT NULL)NAME columns are identical)Parents to be changed are not static (have to be found in database).
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.
The data looks something like this:
ID | DISABLED | NAME | PARAM | PARENT 1 | 1 | qwer | 1 | NULL 2 | NULL | qwer | 2 | NULL 3 | NULL | tyui | 3 | 1 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.