I need to merge to identical tables, but one column (areas_sub_local_obj) has a reference to the Id column (Primary Key). I need a script to insert the data from table 1 into table two and to change the value in the column areas_sub_local_obj to the new Id that corresponds to the original number. Another words: When data is inserted and the original Id was 80 and the new Id is 890 then the rows that contain data in column areas_sub_local_obj that reference to the 80 Id needs to be change to 890 (the new Id) so the parent node relation does not break.

How can I do that?


So we have two tables that look like

create some_table (
  id integer not null primary key,
  areas_sub_local_obj integer
  /* etc */

where areas_sub_local_obj is a reference to some other row in the same table. Is that right? And the merge needs to maintain the reference relationship. I think I see a way to do this with a temporary table, but it takes two passes. Would that be acceptable? I'm not a guru SQL programmer, so there may be a better way that I'm not seeing.

Thanks for your try! Thanks to r937 ! Im posting here his suggestion for others with the same problem:

"update one of your tables, and change the PK by adding a large number to it e.g. UPDATE table2 SET id = id + 4000000

do the same for any other columns which reference the id column
then you can simply


to add the contents of one table to the other "

Thanks again!