In this tutorial, we’ll demonstrate how to update a table in a database.
To determine what records require updating, we’ll compare the database table to an file
of recent updates using the UpdateDetector transformer.
Here is the PostGIS database table, representing parks, in the FME Data Inspector.
Edits to these parks have been captured in this MapInfo TAB file.
This park has been deleted, and the Washrooms and Special Features columns have been updated
for these 2 parks.
Note that ParkID is the key, or match attribute between the 2 datasets.
Let’s create a workspace to update the database table with the more recent MapInfo data.
In Workbench, we have a workspace already set up with the readers.
Now add an UpdateDetector to the canvas.
This is a custom transformer from the FME Hub.
The UpdateDetector detects changes between 2 sets of input features, then routes features
out of the appropriate ports.
Connect the database table to the Original port, and the MapInfo table to the Revised
port.
In the properties of the UpdateDetector, set the Key Attribute to ParkID, and for Attributes
to Match select all.
The next step is to place an attribute on each of the updated and deleted features who’s
value specifies whether that feature will be updated or deleted.
Place an AttributeCreator after the deleted 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.
Place a second AttributeCreator and connect it to the Updated port.
Create another fme_db_operation and set it’s value to UPDATE.
Let’s quickly run the workspace with full inspection to confirm how many features exit
from each port of the UpdateDetector.
Since we’ve already reviewed what updates were in the MapInfo file, it’s expected
to see one feature leaving the deleted port and 2 features leaving the updated port.
Add a PostGIS writer.
Since our table already exists we’ll select the Import from Dataset option for
the table definition.
Add the Downtown_Parks table and open 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 operation.
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 can confirm that Portal Park has been deleted, and Harbour Green and Cathedral Park’s attributes
have been updated.