Skip to Main Content
  • Questions
  • Update a rows in a table with data from other table with only table name and primary key.

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 11, 2017 - 7:01 am UTC

Last updated: September 12, 2017 - 1:50 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

I'm trying to restore a data from a backup data with some special condition for rows.

Example:

Today database in DB1:

City
ID   Name   location    type
1    A      [SDO.geom]  1
2    B      [SDO.geom]  2

District
ID    Name   Atrr1      location     Atrr2      type ...
1     AA     <null>     [sdo.geom]   something  1
2     BB     something  [sdo.geom]   <null>     2
3     CC     something  [sdo.geom]   something  1
4     DD     <null>     [sdo.geom]   something  1

2 day agos data in DB2:

City
ID   Name   location    type
1    A      [SDO.geom]  1
2    B      [SDO.geom]  2
3    C      [SDO.geom]  1
4    D      [SDO.geom]  1

District
ID    Name   Atrr1      location     Atrr2      type ...
1     AA     some       [sdo.geom]   something  1
3     CC     <null>     [sdo.geom]   <null>     1


I want to restore data 2 days ago with 2 conditions:
- only type=1 in every table
- If a field 2 days ago is null and now it has data, then it remain unchanged.

As far as I can go, I wrote a script using MINUS to return a table like (ID is the primary key of each object):
Table     ID    Action
City      3     "add"
City      4     "add"
District  1     "modify"
District  3     "modify" (but nothing change, because 2nd condition)
District  4     "remove"


I already wrote script to remove and add data from backup data. Then the modify is complicated. When using MINUS to compare table City and City@DB2_link I don't know which field is changed and there are some special fields such as SDO_geometry. So how can I write a script to update a whole rows with just table name and primary key (ID) from another database? Thank you.

Update, I added a test case script:
in database db1, current database
create table city (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type  number(5));
create table district (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type     number(5),
          Attr1   varchar2(15),
          Atrr2   varchar2(15)
          );
insert into city values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into district values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');
insert into district values (2, 'BB' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2, 'something', null);
insert into district values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district values (4, 'DD' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');



-- I build up another database db2 which is the back up from 2 day ago.
create table city (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type  number(5));
create table district (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type     number(5),
          Attr1   varchar2(15),
          Atrr2   varchar2(15)
          );
    
insert into city values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into city values (3, 'C' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (4, 'D' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into district values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, null);


-- in db1 again:
CREATE PUBLIC DATABASE LINK db2_link connect to db2 identified by password using ip@10g; 


-- I'm trying to restore all from db2 to db1 only where the type=1 in every tables, and: If a field currently have value in db1, db2 it was null, then it not change.(db2 not override that field on db1, example district id=3)

and Chris said...

To help make things clear, I've loaded the original data into _bkp tables:

create table city (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type  number(5));
create table district (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type     number(5),
          Attr1   varchar2(15),
          Atrr2   varchar2(15)
          );
insert into city values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into district values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');
insert into district values (2, 'BB' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2, 'something', null);
insert into district values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district values (4, 'DD' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, 'something');

create table city_bkp (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type  number(5));
create table district_bkp (
         ID      NUMBER(5) PRIMARY KEY,
         Name     VARCHAR2(15),
         Location SDO_GEOMETRY,
         Type     number(5),
          Attr1   varchar2(15),
          Atrr2   varchar2(15)
          );
    
insert into city_bkp values (1, 'A' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city_bkp values (2, 'B' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 2);
insert into city_bkp values (3, 'C' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into city_bkp values (4, 'D' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1);
insert into district_bkp values (1, 'AA' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, 'something', 'something');
insert into district_bkp values (3, 'CC' , MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)), 1, null, null);


If you want to do a minus on SDO_geometry, you can extract the fields of it in your select to compare them:

select id, b.location.sdo_gtype, b.location.sdo_srid 
from   city_bkp b
minus
select id, c.location.sdo_gtype, c.location.sdo_srid 
from   city c;

ID  LOCATION.SDO_GTYPE  LOCATION.SDO_SRID  
3   2003                                   
4   2003 


But it sounds like you don't really need to compare the individual fields. Just set the current table depending on whether the previous fields were null. I'm not clear if you want to do this at the field level or for the whole row.

Either way, merge can help.

In the update clause, if the old field is null and the new one isn't, set the value to the new field. Otherwise set it to the old one.

select * from district;

ID  NAME  LOCATION                    TYPE  ATTR1      ATRR2      
1   AA    oracle.sql.STRUCT@3c61db2d  1                something  
2   BB    oracle.sql.STRUCT@38a44eec  2     something             
3   CC    oracle.sql.STRUCT@171bd0fb  1     something  something  
4   DD    oracle.sql.STRUCT@a25192f   1                something 
  
merge into district d 
using ( select * from district_bkp where type = 1 ) b
on    ( d.id = b.id )
when not matched then insert 
  values (b.id, b.name, b.location, b.type, b.attr1, b.atrr2 )
when matched then update
  set d.name = case 
                  when ( b.name is null and d.name is not null ) then d.name 
                  else b.name
                end,
      d.location = case 
                  when ( b.location is null and d.location is not null ) then d.location 
                  else b.location
                end,
      d.attr1 = case 
                  when ( b.attr1 is null and d.attr1 is not null ) then d.attr1 
                  else b.attr1 
                end,
      d.atrr2 = case 
                  when ( b.atrr2 is null and d.atrr2 is not null ) then d.atrr2 
                  else b.atrr2 
                end
  where d.type = 1;
  
select * from district;

ID  NAME  LOCATION                    TYPE  ATTR1      ATRR2      
1   AA    oracle.sql.STRUCT@539866f   1     something  something  
2   BB    oracle.sql.STRUCT@1d66878a  2     something             
3   CC    oracle.sql.STRUCT@400e4669  1     something  something  
4   DD    oracle.sql.STRUCT@39aa8c07  1                something 


Merge also has a delete clause. So you can remove the rows from the current table too. Though you need a bit of jiggery-pokery to do this, as Lucas Jellema explains:

https://technology.amis.nl/2006/10/14/have-merge-remove-records-from-target-that-are-not-in-the-source-oracle-10g/

And of course, you're doing this over a DB link. So you'll hit:

merge into district d 
using ( select * from district_bkp@loopback where type = 1 ) b
on    ( d.id = b.id )
when not matched then insert 
  values (b.id, b.name, b.location, b.type, b.attr1, b.atrr2 )
when matched then update
  set d.name = case 
                  when ( b.name is null and d.name is not null ) then d.name 
                  else b.name
                end,
      d.location = case 
                  when ( b.location is null and d.location is not null ) then d.location 
                  else b.location
                end,
      d.attr1 = case 
                  when ( b.attr1 is null and d.attr1 is not null ) then d.attr1 
                  else b.attr1 
                end,
      d.atrr2 = case 
                  when ( b.atrr2 is null and d.atrr2 is not null ) then d.atrr2 
                  else b.atrr2 
                end
  where d.type = 1;

SQL Error: ORA-22804: remote operations not permitted on object tables or user-defined type columns


You can get around this by making a local (materialized) view over the top of the remote table. So the statement only works on local objects:

create or replace view district_bkp_v as
  select * from district_bkp@loopback;

merge into district d 
using ( select * from district_bkp_v where type = 1 ) b
on    ( d.id = b.id )
when not matched then insert 
  values (b.id, b.name, b.location, b.type, b.attr1, b.atrr2 )
when matched then update
  set d.name = case 
                  when ( b.name is null and d.name is not null ) then d.name 
                  else b.name
                end,
      d.location = case 
                  when ( b.location is null and d.location is not null ) then d.location 
                  else b.location
                end,
      d.attr1 = case 
                  when ( b.attr1 is null and d.attr1 is not null ) then d.attr1 
                  else b.attr1 
                end,
      d.atrr2 = case 
                  when ( b.atrr2 is null and d.atrr2 is not null ) then d.atrr2 
                  else b.atrr2 
                end
  where d.type = 1;


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very nice solution for restore data with special condition

A reader, September 12, 2017 - 10:43 am UTC

The solution solve all the problems in my case. It is really helpful. Thank you.
Chris Saxon
September 12, 2017 - 11:05 am UTC

Glad this helped.

Is merge working with foreign key?

Anton, September 12, 2017 - 11:28 am UTC

Hi,
I have a question if the district refer to the city. Or the district have the foreign key parent_id refer to itself. Can it still work? Will the merge affected by the constraints.
Chris Saxon
September 12, 2017 - 1:50 pm UTC

Any rows you insert/update with merge still have to pass constraints on the target table.

So if your target has FKs to other tables, you need to ensure the parent rows are in these first.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.