Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    Update Live db Table From Like Dev db Table

    Code:
    greetings all-
    
    We work on Oracle 11g on a Ubuntu 12.4 Server.  We have a Develpment Oracle DB that is the same as our Live DB.  They are not mirrored in the sense that they have the exact same data all the time.  We have to manually sync the dev db to the live (or certain schema/tables) as we need to or wish to.
    
    I needed to add a new column to the same table on both Live and Dev DBs.  Through a shell script I updated over 1 million records in the table on our Dev db.  I now need to do the same on our Live db.  I do not want to run the script as it does a log of sub-querys to get the info and takes over 12 hours to run.  Now that I have the Dev db table set, I'd like to run a simple query in Oracle to update the Live DB table with the new field values from the Dev DB table/field.
    
    I have public db link set up and it works.  What I need is the query.  I created two test tables to make certain I wouldn't bungle up the tables or the db with a bad query. 
    
    Note, I could import the dev table to the Live db, but that would over-write any current data that has been added since my script last ran.  New records are getting inserted into the new field on Live.  So, a query is what I'd like to use.
    
    
    test_jdc table test on Dev
    
    FIRST                LAST                 NBR                    
    -------------------- -------------------- ---------------------- 
    john                 doe                  1                      
    jim                  foo                  2                      
    joe                  bar                  3                      
    june                 cleaver              6                      
    jane                 jones                4                      
    sue                  que                  5                      
    betty                bop                  7

    test_jdc table on Live DB:

    Code:
    FIRST                LAST                 NBR                    
    -------------------- -------------------- ---------------------- 
    john                 doe                  0                      
    jim                  foo                  0                      
    joe                  bar                  0                      
    june                 cleaver              0                      
    jane                 jones                0                      
    sue                  que                  0                      
    betty                bop                  0
    Would anyone know a query to update all the 0's (zeros) to the appropriate numbers from the Dev db table?

    Doesn't work:
    Code:
    Update webadmin.test_jdc 
      set nbr = (select nbr from webadmin.test_jdc@shadev);
    The '@shadev' is the public db link that allows me to access the Dev db from the Live one.

    Thanks for any suggestions.
    -john

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,621
    Oracle does NOT allow doing DDL across DBLINK.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Nov 2012
    Posts
    4
    I do this sort of thing quite often, although not this type of update.

    Example:
    truncate table schema_name.tableA;
    insert into schema_name.tableA
    (select * from schema_name.tableA@shalive);
    commit;

    As mentioned, I do the above type of query quite often. It is a quick way to refresh a table on our DEV db, if no primary_keys and associated keys from other tables are not used or if no sequences are used. It works.

    I've just never done an update query of this type I need.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,621
    DML (INSERT, UPDATE, & DELETE) are allowed across DBLINK.
    DDL (ALTER, CREATE, etc.) are NOT allowed across DBLINK.
    You do not have to convince me of anything.
    The issue is between you & Oracle.
    When posting problems here it is Good Thing (tm) to use COPY & PASTE;
    so we can see exactly what you do & how Oracle responds
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  5. #5
    Join Date
    Nov 2012
    Posts
    4
    You are correct. I mis-read your DDL. However, I do not think that DDL applies to this. I'm trying to update an already existing table with data from another already existing data using an UPDATE query and a db link.

    I copy/pasted the values of my Dev test table. I copy/pasted the values of my Live test table.
    I copy/pasted a sql I tried that does not work.

    Due to sensitive data, I cannot copy/paste data from the actual tables I need to work on. But, the update sql would basically be the same.

    Thanks for any help.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,621
    >I copy/pasted the values of my Dev test table.
    >I copy/pasted the values of my Live test table.
    I can't issue SQL against what you posted. We need CREATE TABLE & INSERT statements from you for your tables & data.
    >I copy/pasted a sql I tried that does not work.
    See a picture of my car that does not work.
    Tell me how to make my car go.
    >Update webadmin.test_jdc set nbr = (select nbr from webadmin.test_jdc@shadev);
    NBR is a scalar & can only hold a single. if SELECT returns more than 1 row, error gets thrown.
    Which row gets which value "from webadmin.test_jdc@shadev"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  7. #7
    Join Date
    Nov 2012
    Posts
    4
    On my actual tables. This works:

    Code:
    select count(*) from webadmin.users_access_log where ual_content_id is not null;

  8. #8
    Join Date
    Feb 2005
    Posts
    56
    Code:
    Update webadmin.test_jdc 
      set nbr = (select nbr from webadmin.test_jdc@shadev);
    You are selecting many values to populate a single column on a single row.

    You need to select one record from the source to populate one record on the target. Use the WHERE clause to match on FIRST and LAST.

    hth
    Outrider

  9. #9
    Join Date
    Feb 2006
    Posts
    172
    Quote Originally Posted by jconoracle View Post
    Doesn't work:
    Code:
    Update webadmin.test_jdc 
      set nbr = (select nbr from webadmin.test_jdc@shadev);
    We don't know what error message(s) you are getting, but it looks like you need to just add code so that only matching rows are attempted to be updated, for example, if the columns first and last are the tables primary key:
    Code:
    update webadmin.test_jdc prd
      set  prd.nbr = ( select dev.nbr
                        from  webadmin.test_jdc@shadev dev
                       where  dev.first  =  prd.first
                        and   dev.last   =  prd.last )
    where  ( prd.first, prd.last ) in ( select first, last
                                         from  webadmin.test_jdc@shadev )

Posting Permissions

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