Hi all
I have an issue that is probable pretty simple but I seem to be struggling with
I have the following tables
table1 (primary key: inf_id)
table2 (primary key: t2_id, foreign key: inf_id)
table3 (primary key: t3_id, foreign key: inf_id)
table4 (primary key: t4_id, foreign key: inf_id)
so table 3 through 4 are linked up to table1 via the inf_id
I have some changes to make to all 4 of these tables. Before I began I copied the contents of all four tables to a backup table
giving me something like :
tmp_table1
tmp_table2
tmp_table3
tmp_table4
each table can contain about 200 000 rows and I only modified 7000 or so. If I want to copy the original data back to the main tables I could do something like this
DELETE FROM table1;
INSET INTO table1 SELECT * FROM tmp_table1 ;
of course I would need to do table2,table3 and table4 first and do table1 last given that they have foreign keys.
That would work but I find it a bit heavy to delete the whole table simply to replace the few changes I made
Given that I know what fields I affected could I not simply do an update?
let's say I modified the field called DISTANCE in table2
could I not simply do something like this?
UPDATE table2 t2
SET (DISTANCE) = (SELECT tmp2.DISTANCE
FROM tmp_table2 tmp2
WHERE t2.inf_d = tmp2.inf_id AND and t2.t2_id = tmp2.t2_id)
I tried a few things but it keeps failing on me. is an update the way to go or is there a smarter way to do this?
any advice would be appreciated
thanks and I hope I was clear enough :)