In this tutorial, we’ll demonstrate how to update a table in a database.
The updates that need to be performed will be determined by testing for certain values
in the table that needs updating, then setting new values.
Here is the PostGIS database table, representing parks, in the FME Data Inspector.
A few changes need to be made to this data.
Redevelopment is planned for Portal Park, so it will need to be deleted.
A couple of other parks have been reassessed, Harbour Green Park needs to have it’s Washrooms
and Special Features fields need to be updated from Null to No, and the same goes for Cathedral
Park.
In Workbench, we have a workspace already set up with the PostGIS reader.
First we’ll test for Portal Park and set it up for deletion.
Place a Tester on the canvas and test for ParkName equals Portal Park.
Place an AttributeCreator and connect it to the Passed port.
Enter fme_db_operation for the attribute name, and set it’s value to DELETE.
Fme_db_operation is an internal format attribute that is recognized by most database writers.
When a feature gets to the writer with this attribute, it instructs the writer on whether
to perform an update, delete or insert on the matching row in the database.
So, it’s really important to set this specific internal format attribute when doing updates
to a database.
Next, we’ll place another Tester in a parallel stream to test for parks that have null values
for the Washrooms and Special Features fields.
We can pick the Attribute is Null operator for both tests.
Let’s set this up as a composite test, so one or the other test must pass, or both tests
must pass.
And, just as we did before, we’ll need to set up an AttributeCreator to set fme_db_operation
to UPDATE.
We’ll also hardcode a value of N for Special Features and Washrooms.
Since these are the only 2 fields that need updating, let’s place an AttributeKeeper
on the canvas and remove the other other attributes.
By keeping the ones we want we are dropping all others.
ParkID must also be kept as this is the match column to the database table.
Now add a PostGIS writer. Enter the connection parameters.
Select the Import from Dataset option for the table definition since our table already exists.
We'll select fmedata2015 for the schema to read from, and then the downtown_parks table from the table list.
Once the downtown_parks feature type table is added to the canvas, connect up the AttributeKeeper and AttributeCreator to it, and the open up it's properties.
There are 2 parameters we need to set in the Format Parameters tab.
First, set Feature Operation to fme_db_operation.
Note that though it is also possible to specify UPDATE, DELETE, or INSERT here, it is not
recommended, because that means every feature written to this table can only have one of those operations.
So if you have a situation where you have a combination of updates, inserts and deletes,
it’s best to set and use fme_db_operation.
Second, set the match column to ParkID to ensure the matching rows are updated or deleted
in the database table.
Run the workspace.
Inspect the table in the Data Inspector.
We have it open here from before. We expect Portal Park to be deleted once we refresh this table.
It is in fact gone and we can also confirm that Harbour Green and Cathedral Park's attributes have been updated.
Note that the method shown here has us testing for values in the existing table and then
setting new values.
Often, though, updates to a dataset are captured elsewhere in a different file….in this case the updates need
to be compared to the existing table in order to determine what features are to be updated.
The UpdateDetector transformer is very useful here….check out this video to see it in
action….