Skip to main content
Question

Update or insert

  • November 10, 2017
  • 6 replies
  • 325 views

dbklingdom
Contributor
Forum|alt.badge.img+7

Does anyone have a way to Update or insert rather than have Writers that Delete and Insert. Something Oracle Merge? Thx

MERGE Purpose Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

6 replies

Forum|alt.badge.img+2
  • November 10, 2017

Assuming loading into a database, and there is a format attribute called fme_db_operation which is at a feature level and can be defined as INSERT UPDATE DELETE. You just need to calculate the correct value and send to the writer.

Do a search to find articles, there are quite a few on the knowledge center


Forum|alt.badge.img+2
  • November 10, 2017
mark_f wrote:

Assuming loading into a database, and there is a format attribute called fme_db_operation which is at a feature level and can be defined as INSERT UPDATE DELETE. You just need to calculate the correct value and send to the writer.

Do a search to find articles, there are quite a few on the knowledge center

P.s. when I say database, also formats such as Excel.

 

 


dbklingdom
Contributor
Forum|alt.badge.img+7
  • Author
  • Contributor
  • November 10, 2017

Hey Thx!! If I do not have fme_db_operation I assume I can add it with AttributeManager??


Forum|alt.badge.img+2
  • November 10, 2017
dbklingdom wrote:

Hey Thx!! If I do not have fme_db_operation I assume I can add it with AttributeManager??

Exactly! Also if you us the UpdateDetector custom transformer it creates it for you.

 

 


kimo
Contributor
Forum|alt.badge.img+10
  • Contributor
  • December 13, 2017

I assume all db_operations apply to the full record. What if I only want to update one column? Do I have to get out all the other values to preserve them? I have been looking at a discussion using SQL on how to Update or Insert, an "Upsert" is managed in sqlite.

Is a plain update of one field possible with just a key and that field in the writer schema? That would not apply for an Upsert.


Forum|alt.badge.img+2
  • December 13, 2017
kimo wrote:

I assume all db_operations apply to the full record. What if I only want to update one column? Do I have to get out all the other values to preserve them? I have been looking at a discussion using SQL on how to Update or Insert, an "Upsert" is managed in sqlite.

Is a plain update of one field possible with just a key and that field in the writer schema? That would not apply for an Upsert.

To update one field you can have just that column defined on the output feature type. Also check that the feature type parameters are set correctly for updating geometry or not. As you said this wouldn't work for upsert.

 

 


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