Home » SQL & PL/SQL » SQL & PL/SQL » moving column from one table to another...
moving column from one table to another... [message #18621] Thu, 07 February 2002 10:15 Go to next message
Jared
Messages: 10
Registered: June 2001
Junior Member
I have two tables

Table A:

ID NUMBER;
VALUE_A NUMBER;

TAble B:

ID NUMBER;
VALUE_B NUMBER;

Assuming VALUE_B was just added to Table B and is empty for all records, I want to move VALUE_A to VALUE_B when the IDs are equal. How can I do this in SQL? Thanks in advance!
Re: moving column from one table to another... [message #18622 is a reply to message #18621] Thu, 07 February 2002 11:23 Go to previous message
INTROV
Messages: 20
Registered: February 2002
Junior Member
Write a procedure that will retrieve all records from table B. Using tableB.ID as reference, get the value of VALUE_A from table A, and when found update the
record with column VALUE_A to VALUE_B in table B. The simplest way is that you can write the procedure using a cursor.

create or replace procedure upd_TableB is

v_id1 number;
v_id2 number;
v_valueA varchar2(30);
v_valueB varchar2(30);
v_rowid varchar2(50);

cursor upd_table1 is
select * from tableA ;

v_TempRecord upd_table1%RowType;
BEGIN
FOR v_TempRecord in upd_table1 LOOP
v_id1 := v_TempRecord.ID;
v_valueA := v_TempRecord.VALUE_A;
BEGIN
select id,value_b, rowid
INTO v_id2,v_valueB,v_rowid
from tableB
where id = v_id1;

update tableB set value_B = v_valueA
where rowid = v_rowid;
commit;

exception
when no_data_found then
NULL
end;
END LOOP;
end;
/
Previous Topic: dynamic pl/sql method 4
Next Topic: HELP ME!! CURSOR error while trying to FETCH.
Goto Forum:
  


Current Time: Sun Sep 19 05:01:02 CDT 2021