Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Insert, Update, Delete?

  1. #1
    Join Date
    Aug 2012
    Posts
    15

    Question Insert, Update, Delete?

    Hi,

    I currently have two databases integrated with pentaho. Database 1 is using 'table input' step and moving into database 2 which has the 'insert/update' step.
    Now, this works great for insert/update.

    My problem now is that if I were to delete a row from database 1, how do I get it to delete from database 2 as well? The 'insert'update' step does not automatically delete the rows that have been deleted from database 1.

    Please help!

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by jasonchua View Post
    My problem now is that if I were to delete a row from database 1, how do I get it to delete from database 2 as well?
    Usually this transaction replication is handled by the DBMS itself (replication or triggers) but I presume this is an PDI exercise and not a real life task...

    I assume both tables shall hold the same data rows in the end.
    Since you don't know which rows were deleted from table1 you must find out, first.
    One way to accomplish this is by calculating a set difference:
    Read all keys from table1, read all keys from table2, both in ascending order.
    Use a Merge Rows step and Filter those rows marked as deleted.
    Now you can use the Delete step to remove these rows from table2.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Aug 2012
    Posts
    15

    Default

    Thanks for that. I'll look into it

  4. #4
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    actually - take a look at the "synchronize after merge" and "merge rows diff" steps. Or if you already know the rows to delete then simply use the "delete" step!

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.