Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements.

I have two databases with a mirrored table. One of them has some column data missing from it. I tried truncating the data from that table but it has foreign keys associated and SQL doesn't let me drop it.

Is there a way to export the data from the larger/fuller table in the form of "Update Table..." commands? I tried googling first, failed. I bet there's a solution for this out there.

If nothing else, I am thinking of making a small python script that modifies the Insert command set into an Update command set after export.

share|improve this question
    
assume I have to export the data... Things such as "Update table .. where (select something from other table)" is not allowed. This is because I have to create the script and send it to someone... –  Nitin Mar 14 '12 at 17:25
add comment

3 Answers 3

up vote 2 down vote accepted

Not using a built in command, but...

If an UPDATE really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.

Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:

drop table t1;
drop table t2;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
create table t2 as (select * from t1);
update t2 set c2 = null where c1 in (3,6);
select * from t1;
select * from t2;

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1 
join t2 on t1.c1 = t2.c1
where t1.c2 <> t2.c2 OR t2.c2 is null;

If you cannot query the remote database, you can update every row like this:

drop table t1;
create table t1 as (select level c1, level c2 from dual connect by level <=7);

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1;
share|improve this answer
    
Leigh... That seems to be the most complete solution I've seen. Thanks! I've also submitting my own version of the solution. I implemented a small script that converts a series of insert commands into update commands... –  Nitin Mar 15 '12 at 17:10
add comment

What you want is MERGE.

See Oracle docs for MERGE . Allows you determine if a row should be updated or inserted.

SQL Developer doesn't support generating MERGE statements today. You can go vote for that feature to make a future version though!

share|improve this answer
add comment

Thank you all for your contribution.. This is the way I resolved it and I hope that if someone else needs this, they can find it here -

The following is a python script that converts the insert commands into Update commands...

import sys
import re


file1 = open('import.sql')
file2 = open('update.sql','w')
for line in file1:
    #print line
    m = re.search("values \((\d*)",line)
    #print m.group(1)
    code_id = m.group(1)
    #there are 8 columns in the table and we need the last 2
    all_data = re.search("values \((.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)\)\;",line)
    #print all_data.group(7)
    code_gui = all_data.group(7)
    #print all_data.group(8)
    code_ind = all_data.group(8)
    print "UPDATE CODES SET CODE_GUI_MSG="+code_gui+", CODE_INDICATOR="+code_ind+" WHERE CODE_ID="+code_id+";"
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.