Re: PL/SQL : How would you do this ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 21 Nov 2001 15:42:46 -0800
Message-ID: <9the5m0lu5_at_drn.newsguy.com>


In article <gmVK7.4128$636.1200576_at_news02.optonline.net>, "jane" says...
>
>I want to solve this problem in PL/SQL
>
>I accidentally updated EMP.JOB to 'BUSBOY' (all columns)
>But I have the previous result set saved in ascii like...
>
>EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
>----- ---------- --------- ----- --------- ----- ----- ------
> 7369 SMITH HITMAN 7902 17-DEC-80 800 20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
> 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
> 7566 JONES MANAGER 7839 02-APR-81 2975 20
> 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
> 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
>
>On 8i, I put the JOB values and EMPNO values into collections, matching the
>orders,
>and EXECUTE IMMEDIATE the updates in a loop. Works.
>
>Is there a better way to restore these values back to it's original records
>?
>How about on V8 ?
>
>thanks
>jane
>

probably wouldn't use plsql for this.

use sqlldr to load the empno and job columns into a table "TEMP"

update emp

   set job = ( select job from temp where temp.empno = emp.empno )  where exists ( select job from temp where temp.empno = emp.empno ) /

In 9i, external tables and a single update can do this (no sqlldr, the create table contains a control file)
>
>
>
>
>
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Thu Nov 22 2001 - 00:42:46 CET

Original text of this message