Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: update on table from select on other db

  1. #1
    Join Date
    Dec 2006
    Posts
    20

    Default update on table from select on other db

    Hi,

    i have a table in a db and want to execute a select for each row in this table on a table in another db. The result of this select should be written back as an update into the table of the first db.

    Can anybody help me out and tell me what is the best approach for this.

    I tried out several thing but didn't get the result i expected

    Cheers

    Thorsten

  2. #2
    DEinspanjer Guest

    Default

    It sounds like you might be over-simplifying the question.
    Are you only updating the records if they have changed? What about new records? What about deleted records?

  3. #3
    Join Date
    Dec 2006
    Posts
    20

    Default

    OK, i understand your question, but it is nearly that simple.

    i have a source table with four coloumns. two of them are needed (tabname, tabschema) to contruct the select against the table on the other database. The select ist executed for each row in this table. Every row should be updated after the select. There are no additional rows because the select delivers only one value for each row.

    The source row should then be updated with this value. Perhaps i need some kind of temporary table or somethin like that. At this point it is not clear to me how to put the select result and the update together row by row.

    Thorsten

  4. #4
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Wouldn't this be this kind of scenario:

    1) Table input from the table to be updated

    2) Table input with "Replace variables" and "Execute for each row" enabled" (and using "?" as required, see http://wiki.pentaho.com/display/EAI/Table+Input for an example)

    3) Update or Insert/Update on the first table.

    If you need to delete, you could probably use a filter and a "Delete step".

    Cheers!

  5. #5
    Join Date
    Dec 2006
    Posts
    20

    Default

    Hi,

    sorry for the late reply but i was not able to test it until now. I changed various things according to your suggestions.

    One thing still remains unclear to me.

    When the select statement is executed for every row and returns for example the count value of a table. How does the update step knows this result? What do i have to put into the "update fields" part of the update step??

    Can anybody explain this to me in more detail or provide an link to some examples??

    Thorsten

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.