| Re: Restore using an update command [message #374921] |
Wed, 11 July 2001 16:32 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Questions? Are the records in the backup table exist in the main table currently? Which table did you perform modification to...temp table or main table...I'm guessing main table? What exactly did you modify? Bare in mind, if you modify the field or fields that distinguish the uniqueness of a record, then you can not do an update, you need to do an insert and not only do you have to do an insertion, you have to idenify those changed data(now I'm assume is junk data) to be delete from the main table. Once you verify that the main table do not contain records in temp table, then you can do this(I am assuming that you want all data from temp_table):
insert into main_table
select * from temp_table;
This will restore all records from the temp table to main table.
Another option you can do if you still like to do update to select fields. I am assume you made modification in main table to select fields. Create a procedure to update it. Here is an example:
SET SERVEROUT ON
DECLARE
v_column1 VARCHAR2(7);
v_column2 CHAR(11);
v_column3 NUMBER(6);
v_column4 VARCHAR2(29);
v_column5 VARCHAR2(60);
so on and so on if necessary
CURSOR C1 IS
SELECT column1,
column2,
column3,
column4,
column5,
column6
FROM temp_table;
BEGIN
OPEN C1;
LOOP
FETCH C1 into v_column1,
v_column2,
v_column3,
v_column4,
v_column5,
v_column6;
EXIT WHEN C1%NOTFOUND;
/* I'm assuming that you want to update two fields(columns) due to modification */
UPDATE main_table
SET main_table.column5 = v_column5,
main_table.column6 = v_column6
WHERE main_table.column1 = v_column1
AND main_table.column2 = v_column2
AND main_table.column3 = v_column3
AND main_table.column4 = v_column4;
/* Don't put commit in unless you're sure that this is what you want to update */
COMMIT;
END LOOP;
CLOSE C1;
END;
/
Just a thought: If you ever have to do some testings or modifications to any select data from a table, you should use the data from the temp table to play around with, therefore you can always get orginal data from the main table and you don't have worry with messing up orginal data in regarding to those select records or other records in the main table or even skip restoring process together. Besides, if you use a temp table to test your codes, it is easier to change the part of codes that state the name of the table than to deal with the data modification.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
