I have two tables, T_A and T_B, which primary keys ID_A and ID_B
Currently T_A contains the FK to T_B (ie it contains the reference to ID_B) but I would like to reverse this by adding the FK and constraint to T_B, running an update script to copy the (ID_A, ID_B) from T_A to (ID_B, ID_A) in T_B then drop the column from T_A
I don't have any problems with adding the columns etc, but I'm not sure how to phrase the actual UPDATE statement.
I can revert to Java is necessary, but this is a single step in the middle of a bunch of other SQL operations, and moving to Java and keep them in order would mean also running the SQL statements in Java, which is what I am trying to avoid. This is supposed to be a single deployed script.
I don't understand your suggestion, can you expand it at all?
A long time ago I did the 'dynamic SQL built by a concatenated SQL statement' but I can't remember how exactly and I'm not sure if it will work in MaxDB. My attempt looked like this:
SELECT 'UPDATE T_A SET ID_B = ' | T_B.ID_B | ' where ID_A = ' | T_B.ID_A from T_B