| moving column from one table to another... [message #18621] |
Thu, 07 February 2002 10:15  |
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  |
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;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
