Skip to main content
Question

Updating an individual field in a database table

  • February 16, 2021
  • 6 replies
  • 564 views
markcoopersdc
Contributor
Forum|alt.badge.img+6

Hi

I am running a workbench to update a single field in an existing table. I have the following settings:

 

Feature operation: Update

Table handling: Existing

Match Columns: Set to two fields that identify the record to update

 

I have an attribute manager before the writer to only show the field I want to update and the match columns. Therefore, those three columns in the writer have the green indicator and the rest are red as they are not connected.

 

My question is, will this writer write to the match columns too? I know by definition of them being match columns, it will only overwrite with the same value, but I would prefer it to leave them alone altogether.

 

Is this possible, perhaps if I remove the match columns in the User Attributes tab, is there another way, or should I just trust it and leave it as it is?

 

Many thanks

6 replies

david_r
Celebrity
  • david_rCelebrity
  • February 16, 2021

The writer will update all the columns, the attributes flagged as red will be set to null as they won't have a value.

My recommendation would be to use the DatabaseUpdater transformer, where you'll have more control of what happens.

markcoopersdc
Contributor
Forum|alt.badge.img+6
  • markcoopersdcContributor
  • Author
  • Contributor
  • February 16, 2021

Hi. Thanks for your quick reply. Is that definitely the case? This article suggests if you don't connect anything to the field, nothing will update:

 

https://community.safe.com/s/article/update-only-certain-columns-in-a-database-table

 

"To avoid updating specific attributes we can simply remove them from the data written to the database table. This is done with an AttributeManager transformer.

 Inspect the AttributeManager parameters. To avoid updating fields it is simply a case of removing the matching attributes. In this case we want to avoid updating the visitorcount and treecount fields, so remove those attributes in the AttributeManager"

 

My question was more would the match fields be updated too and, if so, how that could be prevented.

 

That said, in this case, I think it would be best to use the database updater as you can categorically tell it which fields to update. I did not know this even existed - like usual with FME there are many ways to carry out a task, but this seem the best. Thank you.

 

 

 

david_r
Celebrity
  • david_rCelebrity
  • February 16, 2021
markcoopersdc wrote:

Hi. Thanks for your quick reply. Is that definitely the case? This article suggests if you don't connect anything to the field, nothing will update:

 

https://community.safe.com/s/article/update-only-certain-columns-in-a-database-table

 

"To avoid updating specific attributes we can simply remove them from the data written to the database table. This is done with an AttributeManager transformer.

 Inspect the AttributeManager parameters. To avoid updating fields it is simply a case of removing the matching attributes. In this case we want to avoid updating the visitorcount and treecount fields, so remove those attributes in the AttributeManager"

 

My question was more would the match fields be updated too and, if so, how that could be prevented.

 

That said, in this case, I think it would be best to use the database updater as you can categorically tell it which fields to update. I did not know this even existed - like usual with FME there are many ways to carry out a task, but this seem the best. Thank you.

 

 

 

It may be that my knowledge about this is a bit outdated. I've had some unexpected issues using database updates on the regular writer in the past, but it could well be that it's been improved since then.

Personally I tend to only use the DatabaseUpdater or the SQLExecutor now, at least then the expected behavior is very clear, also for those looking at my workspace down the line.

markcoopersdc
Contributor
Forum|alt.badge.img+6
  • markcoopersdcContributor
  • Author
  • Contributor
  • February 16, 2021

I agree it seems the best way. Unfortunately, FME crashes every time I run the DatabaseUpdater (v2020.1). Just downloading 2020.2 to see if that resolves the issue.

M
Forum|alt.badge.img+2
  • markatsafe
  • February 16, 2021

@markcoopersdc​  The article you referenced, Updating Databases: Updating Individual Fields of a Record, should give you the results you need, although I'm not sure if the match fields are also updated. Although that shouldn't matter since the value will be exactly the same - otherwise you wouldn't get a match.

markcoopersdc
Contributor
Forum|alt.badge.img+6
  • markcoopersdcContributor
  • Author
  • Contributor
  • February 16, 2021
markatsafe wrote:

@markcoopersdc​  The article you referenced, Updating Databases: Updating Individual Fields of a Record, should give you the results you need, although I'm not sure if the match fields are also updated. Although that shouldn't matter since the value will be exactly the same - otherwise you wouldn't get a match.

Thats what I thought. I think Im overthinking it!!

Reply


Helpful Members This Week

Community Stats

31,241
Posts
118,446
Replies
38,833
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.