In my 11.2 oracle database I have a 2 tables as master detail relation.
The detail table basically looks like this:
SQL> BEGIN
2 DBMS_RANDOM.initialize( 1 );
3 END;
4 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL>
SQL> SELECT MOD( LEVEL, 2) AS master_fk
2 , DBMS_RANDOM.random AS detail_pk
3 , LEVEL * LEVEL AS the_value
4 FROM DUAL
5 CONNECT BY LEVEL < 19
6 ORDER BY 1, 3;
MASTER_FK DETAIL_PK THE_VALUE
---------- ---------- ----------
0 1547845136 4
0 -2,092E+09 16
0 -1,169E+09 36
0 683327672 64
0 -1,126E+09 100
0 -396519936 144
0 1441185574 196
0 1791341589 256
0 -7672716 324
1 1077884071 1
1 -578452374 9
1 278538322 25
1 1650499515 49
1 823758384 81
1 500727982 121
1 272927406 169
1 -279867116 225
1 2016444776 289
18 Zeilen ausgewõhlt.
SQL>
I need to update the detail for one master record with the details of another master record. (I cannot simply delete the target details because there are triggeres preventing deletes...) The number of detail records is always the same for source and target master (but is not always 9). The desired output is:
MASTER_FK DETAIL_PK THE_VALUE
---------- ---------- ----------
0 1547845136 1
0 -2,092E+09 9
0 -1,169E+09 25
0 683327672 49
0 -1,126E+09 81
0 -396519936 121
0 1441185574 169
0 1791341589 225
0 -7672716 289
1 1077884071 1
1 -578452374 9
1 278538322 25
1 1650499515 49
1 823758384 81
1 500727982 121
1 272927406 169
1 -279867116 225
1 2016444776 289
Is it possible to do this with a single bulk update?
There is no need to keep the order of the target detail PKs (regarding the order of the values) as long as each source value (master 1) is assigned to a target value (master 0)
bye
TPD