Re: PL/SQL : How would you do this ?
Date: Fri, 30 Nov 2001 03:40:55 GMT
Message-ID: <bdDN7.1266406$si5.58181277_at_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 casefrom emp_table a, temp b
where a.empno=b.empno;
spool off
_at_update.sql
commit; --(you might check first, but heck they were wrong in the first place....)
[Quoted] 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)
> >
> >
> >
> >
> >
> >
> >
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
> --
> Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
> Received on Fri Nov 30 2001 - 04:40:55 CET