I am using oracle 10g and I have information present like below:

Here we have codes and set. All codes will be part of some set. Requirment is to all the codes below to set must have the same information as set ( same postal and street as of the set).

so it should look like this

I have written below plsql to perform, it seem to working fine but since I have 50 such column similar to postal and street. I was wondering if this can be done via SQL or better plsql.

for i in (select code, bill, Deliver , set  from table)
      loop
      
        select code , postal, street into lcode, lpostal, lstreet from table where code=i.set;
      
        if ( i.set <> i.ship) then
          update table set postal=lpostal,  street=lstreet  where code=i.ship;
        end if;
    
        if ( i.set <> i.bill) then      
               update table set postal=lpostal,  street=lstreet  where code=i.bill;                      
        end if;
    end loop;  

CodePudding user response：

It looks like you are fighting an inappropriate data model here. The table is not normalized and so whenever you see divergences, you must get rid of them via updates. If this assumtion is true, then the real answer is: fix the data model.

As to updating the data, this looks like a simple update to me, only that you want to update a tuple (postal and street belonging together):

update mytable t
set (postal, street) =
(
  select t2.postal, t2.street
  from mytable t2
  where t2.code = t.set and t2.set = t.set
);