Copy data from a column in some rows to another column in other rows in oracle

Question

quite the same question as here : Copy data from one existing row to another existing row in SQL?

but in Oracle, where update ... from and update t1, t2 are not supported.

I'll repeat it here in my own words ; I have a table T, which looks like this :

table

and as the arrow shows it, I want to copy everything from r where c = 1 to e where c = 2, with t matching.

I have the select statement to get what I want to copy :

select 
  told.t, 
  told.r
from 
  T told 
  inner join 
  T tnew
on 
  told.t= tnew.t
where 
  told.c = 1
  and 
  tnew.c = 2

I just don't know how to put this together in an update. An Oracle update, specifically.

A.B.Cade · Accepted Answer · 2012-01-30 14:32:07Z

4

try this:

update T tnew
set tnew.e = (select told.r from T told where told.c = 2 and told.t = tnew.t)
where tnew.c = 1

answered Jan 30, 2012 at 14:32

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zonko Over a year ago

Well... thanks. It seems a lot easier now that I know the answer !

Ben · Accepted Answer · 2012-01-30 14:56:17Z

0

Sounds like the time for a bulk collects! Not as pretty as AB Cade's solution but more efficient.

declare

  c_data is
   select t1.rowid as rid, t2.r
     from my_table t1
     join my_table t2
       on t1.t = t2.t
    where t1.c = 2
      and t2.c = 1
          ;

   type t__data is table of c_data index by binary_integer;
   t_data t__data;

begin

   open c_data;
   loop

      fetch c_data bulk collect into t_data limit 25000;

      exit when t_data.count = 0;

      forall i in t_data.first .. t_data.last loop
         update my_table
            set e = t_data(i).r
          where rowid = t_data(i).rid
                ;

      commit;

   end loop;
   close c_data;

end;
/

answered Jan 30, 2012 at 14:56

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Collectives™ on Stack Overflow

Copy data from a column in some rows to another column in other rows in oracle

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related