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
+8- Enthusiast
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.
+19- Contributor
Use the SqlCreator or SqlExecuter to do your query in Postgres itself or use the Joiner or theFeatureJoiner as an alternative.
- 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.
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.