Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    23

    Unanswered: update a table in one schema from a table in another schema

    Using DB V7.2, and in the same database:

    I have a table A in Schema A and a table B in Schema B.

    How can I update table B with entries that table A has and table B does not.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Posts
    188

    Re: update a table in one schema from a table in another schema

    Originally posted by globetrotcom
    Using DB V7.2, and in the same database:

    I have a table A in Schema A and a table B in Schema B.

    How can I update table B with entries that table A has and table B does not.

    Thanks
    Make sure that your loging user has access to both schemas but you can try the follwing, perhaps I am wrong..

    update schemab.table
    set schemaB.table.column = schemaA.table.column
    where
    schemaB.table.column = schemaA.table.column

    I think this should work

  3. #3
    Join Date
    Feb 2003
    Posts
    20

    Re: update a table in one schema from a table in another schema

    As long as you have your tables in the same database (Forget about Federated DB for a while), you can perform any join between your tables. Just use the fully qualified table name. If table name is TABLENAME and schema name is SCHEMA_NAME, then the fully qualified name is SCHEMA_NAME.TABLENAME.

    I am sorry, I can not get the second part of your query.

    What do you want to update - a column for all rows or create records in schemaB.tableB which are not present in schemaA.tableA.

  4. #4
    Join Date
    Jan 2004
    Posts
    23
    I want to insert new rows in table B that are not in table A, based on the values in certain combination of columns in table A and table B.

  5. #5
    Join Date
    Feb 2003
    Posts
    20
    Here it is:

    insert into schemaB.tableB
    (column1,column2)
    select (column1, column2)
    from schemaA.tableA
    where column1 not in (select column1 from schemaB.tableB);

    OR

    insert into test2 (col1,col2) select a.col1, a.col2 from test1 a, test2 b where a.col2!=b.col2;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •