I need to update 5184 rows in a Postgres table. These lines have a code and this code is linked directly to another table that uses this code. What would be the best way to update table 1 and after this update, update table 2 using the code I updated in table 1?
Thank you
Solved
Update rows in Postgres database
+9- Enthusiast
- 145 replies
Best answer by david_r
Personally, my preference would be for the SQLExecutor and using a proper database transaction to ensure atomic updates, even in the event that FME crashes for some reason. For example something like:
BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master set master_id='foo2' where master_id = 'foo';
UPDATE detail set master_id='foo2' where master_id = 'foo';
COMMIT;You may have to recreate the foreign key constraints first and set them to DEFERRABLE, if that's not already the case, e.g.
ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id)
REFERENCES master(master_id) DEFERRABLE;Also, you have to be very careful about not assigning a new value that's already used by other records. That might mangle your relationships.
An alternative solution would be to use cascading updates in the database. If the constraint hasn't already been set up with ON UPDATE CASCADE, you'll have to recreate the foreign key constraint first:
ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id)
REFERENCES master(master_id) ON UPDATE CASCADE; It's then simply a matter of updating the 'master.master_id' value and the 'detail.master_id' value will get updated automatically.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+21- Contributor
- 305 replies
Use the SqlCreator or SqlExecuter to do your query in Postgres itself or use the Joiner or theFeatureJoiner as an alternative.
- 8391 replies
- Best Answer
Personally, my preference would be for the SQLExecutor and using a proper database transaction to ensure atomic updates, even in the event that FME crashes for some reason. For example something like:
BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master set master_id='foo2' where master_id = 'foo';
UPDATE detail set master_id='foo2' where master_id = 'foo';
COMMIT;You may have to recreate the foreign key constraints first and set them to DEFERRABLE, if that's not already the case, e.g.
ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id)
REFERENCES master(master_id) DEFERRABLE;Also, you have to be very careful about not assigning a new value that's already used by other records. That might mangle your relationships.
An alternative solution would be to use cascading updates in the database. If the constraint hasn't already been set up with ON UPDATE CASCADE, you'll have to recreate the foreign key constraint first:
ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id)
REFERENCES master(master_id) ON UPDATE CASCADE; It's then simply a matter of updating the 'master.master_id' value and the 'detail.master_id' value will get updated automatically.