Can Oracle SQL developer export data as a set of Update commands?

Question

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.

Answer 1

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;

Answer 2

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!

Answer 3

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+";"