Try to Update record, Insert if not found

desmond

• February 03, 2011 00:00

• Answered

Follow

Hi, I'm building a graph that reads from a remote database, does a simple transform, and loads the information into my local database. I need Clover to attempt an update of my existing data and, if no rows are found to update, insert a new row. I can currently update properly, but I don't know how to tell Clover to perform an insert if no rows are updated (the update operation "succeeds" even if no rows are updated, so I can't use error handling).

I couldn't find a cookbook recipe for this "upsert." Is there a way to try the update first and then do the insert?

thanks
desmond

• 

  avackova

  • February 07, 2011 12:25

  0

  Comment actions Permalink

  Hello Desmond,
  you can use dictionary for storing the information about number of processed records:updateInsert.png

  • updateInsert.png
• 

  julrych

  • February 17, 2011 05:27

  0

  Comment actions Permalink

  Hi Desmond,
  
  the idea behind upsert implementation split the data into two/three parts
  * records you already have in local database -> update them in local database
  * records you do not have in local database yet -> insert them into local database
  * (possibly also records, which have been deleted from the remote database -> delete them from your local database).
  The record identification is usually done by primary key.
  
  Implementation can be done in several ways. Most convenient way is using DataIntersection. You can get similar results using any of Join operations (ExtHashJoin, MergeJoin or DbJoin) depending on the expected volume of data coming from remote database and local database.
  
  Please find below two sample implementations - one using DataIntersection and one using ExtMergeJoin.

  • Upsert.png
• 

  julrych

  • December 16, 2011 18:12

  0

  Comment actions Permalink

  Upsert implementation with DBJoin; offers great performance especially when input file size (in number of records) is fraction of the database table size (in number of records).

  • Screenshot.png
• 

  cassydeb

  • February 02, 2016 07:11

  0

  Comment actions Permalink

  hi,
  I don't see join type = Inner join in DBJoin component, can you attach a .grf please?
  Thanks for your help.
• 

  slechtaj

  • February 03, 2016 19:10

  0

  Comment actions Permalink

  Hi,
  
  Inner join is the default join type. So if Left outer join is false, which is its default value, the inner join type is automatically used.
  
  Hope it helps.
• 

  cassydeb

  • February 15, 2016 22:01

  0

  Comment actions Permalink

  Thank you.
  
  But I still have a problem with update.
  When I debug the edge between DBJoin and DBOutputTable (update) and view data, it's the data of the table (Port 0 joined records)...So it try to update data with data already in table not the data of the .csv file!
  
  It's maybe a problem with "Field Mapping" property of DBOutputTable that I set like documentation ($CloverField:=DBField)
  $COUNTRY_CODE:="COUNTRY_CODE";$WORDING:="WORDING";...
  
  How to set Field Mapping property to update table with the data of .csv file, not with the data already present in the destination table?
• 

  cassydeb

  • February 16, 2016 23:51

  0

  Comment actions Permalink

  I understand the problem...In these screenshots there is no information about transformation.
  
  Thanks to this post:
  viewtopic.php?f=4&t=6500&p=9954&hilit=upsert#p9954
  
  I have to transform data in DBJoin to get ouput data as input data.
  

  $out.0.* = $in.0.*;

  in transform function.

Please sign in to leave a comment.