aspose file tools*
The moose likes JDBC and the fly likes SQL update to reverse parent-child relationship Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL update to reverse parent-child relationship" Watch "SQL update to reverse parent-child relationship" New topic
Author

SQL update to reverse parent-child relationship

David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Mar 06, 2001
Posts: 13459

You're quite right jeanne, our solution is just like you said.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL update to reverse parent-child relationship
 
Similar Threads
CMR fights relational databases
DAO class design
@PrimaryKeyJoinColumns does not work
OneToOne composite key
same primary key as foreign key in two table[Hibernate Mapping]