If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Update Live db Table From Like Dev db Table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
>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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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 )
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On