• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL update to reverse parent-child relationship

 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thoughts?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave,
Are you using a database that supports selects in the update statement?

For example:
update table set new_fk_ref = (select pk from other_table where ...)

If not, you could always loop through in Java and issue JDBC calls one at a time.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're quite right jeanne, our solution is just like you said.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic