Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Insert/Update: Insert if not exist or update if at least one column changes

  1. #1
    Join Date
    Oct 2015
    Posts
    5

    Question Insert/Update: Insert if not exist or update if at least one column changes

    Hi,

    I want to perform a insert or update (syncronization between source and target tables) attending to a several columns:

    Take for example the following scenario:

    source Table: marketA

    ID_B ProductA PriceA
    1 Banana 10
    2 Apple 4

    target Table: marketB

    ID_B ProductB PriceB
    1 Banana 10
    2 Apple 4

    In this moment as you can see the target table(marketB) has the same content with target A.

    If i change in my source marketA the product name and/or price, i would like to update my target table:

    source Table: marketA

    ID_A ProductA PriceA
    1 Banana 10
    2 Apple -> Golden Apple
    4-> 6

    it is excepted that an update on target table (marketB) should be perfomed,like:

    target Table: marketB

    ID_B ProductB PriceB
    1 Banana 10
    2 Golden Apple
    6
    It will insert only if ID is different
    I tried the Insert Update but doesn't seems to work. the keys to lookup the values should be i suppose:

    TableField | Comparator | StreamField
    ProductB | = | ProductA
    PriceB | = | PriceA
    ID_B | = | ID_A

    Update fields:

    TableField | StreamField

    ProductB | ProductA
    PriceB | PriceA


    What am i missing?
    Using Kettle PDI what is the best approach to perform a insert/update attending to the mentioned scenario knowing that will only update if at least one column that is not a primary key changes and insert if pkey doesn't exist on target table??

    Any suggestion would be great
    Thanks in advance.
    Last edited by epascoal; 11-27-2015 at 02:14 PM. Reason: title was wrong

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by epascoal View Post
    the keys to lookup the values should be i suppose:

    TableField | Comparator | StreamField
    ProductB | = | ProductA
    PriceB | = | PriceA
    ID_B | = | ID_A

    Update fields:

    TableField | StreamField

    ProductB | ProductA
    PriceB | PriceA
    You only want to look up on ID_B=ID_A
    If you include Product_B=Product_A, you will get no matches (because "Apple" won't match "Golden Apple")

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.