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

From: KandoCoder <yewneek_at_iwon.com>
Date: Fri, 30 Nov 2001 03:40:55 GMT
Message-ID: <bdDN7.1266406$si5.58181277@typhoon.kc.rr.com>

On bigger tables, this can take some time - so have also loaded the job and empno in to a temp table
then ran a sql, that generated an update script using rowid. Using this method updated thousands of rows a second (hot box though...) but on big tables (>5G) , far faster..
obviously the table has to be quiet in order to do this, or maybe not - depending on the sit.

pseudo code...
(I can't remember right now if chr(45) is the ' or chr(39) is... - oh well..)

set head off
set pagesize 0
set linesize 512
spool update.sql
-- the next should create lines like this: -- update emp_table set job='REAL_JOB' where rowid='SOMEROWID' and empno=7512; --empno not needed, but for grins... select 'update emp_table set job = '||chr(45)||b.job||chr(45),

         'where rowid = '||chr(45)||a.rowid||chr(45),
         ' and empno = '||b.empno|| ';',    -- just in case

from emp_table a, temp b
where a.empno=b.empno;
spool off
@update.sql
commit; --(you might check first, but heck they were wrong in the first place....)

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news: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@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 29 2001 - 21:40:55 CST