Help with Update statement 1

[AlStl]

Guys,

Need help with updating a table.

My table contains data as follows (primary key : EQUIP & JOBID)

EQUI JOBID RDate Prior_JOBID
XXX1 SSS1 1/1/2010
XXX1 PPP1 2/2/2009
XXX1 YYY1 1/1/2007
YYY1 HHH1 2/2/2011
YYY1 QQQ1 3/3/2010
YYY1 UUU1 4/4/2009
YYY1 TTT1 1/1/2008


I want to write an update statement that will grab the prior JOBID of each record based on RDate and will update the table in following manner:

EQUI JOBID RDate Prior_JOBID
XXX1 SSS1 1/1/2010 PPP1
XXX1 PPP1 2/2/2009 YYY1
XXX1 YYY1 1/1/2007
YYY1 HHH1 2/2/2011 QQQ1
YYY1 QQQ1 3/3/2010 UUU1
YYY1 UUU1 4/4/2009 TTT1
YYY1 TTT1 1/1/2008

thx.

Al

 

[taupirho]

SQL> desc tom;
Name Null? Type
----------------------------------------- -------- --------------------------
X NUMBER
DTE DATE
Y VARCHAR2(1)
PREV_Y VARCHAR2(1)

SQL> select * from tom order by x, dte desc;

X DTE Y P
---------- --------- - -
1 01-JAN-11 A
1 01-JAN-10 B
1 01-JAN-09 C
2 01-JAN-11 Z
2 01-JAN-10 Y
2 01-JAN-09 Z
3 01-JAN-11 B
3 01-JAN-10 C
3 01-JAN-09 D

9 rows selected.

SQL> update tom t1
2 set prev_y =
3 (
4 select t2.py from
5 (
6 select x,y,dte,rowid rid,
7 lead over(partition by x order by dte desc) py
8 from tom
9 ) t2
10 where t1.rowid = t2.rid
11 )
12 /

9 rows updated.

SQL> select * from tom order by x,dte desc
2 /

X DTE Y P
---------- --------- - -
1 01-JAN-11 A B
1 01-JAN-10 B C
1 01-JAN-09 C
2 01-JAN-11 Z Y
2 01-JAN-10 Y Z
2 01-JAN-09 Z
3 01-JAN-11 B C
3 01-JAN-10 C D
3 01-JAN-09 D

9 rows selected.

SQL>


In order to understand recursion, you must first understand recursion.

 

[AlStl]

Taupirho,

Thanks a lot !

I read up on concept of recursive relationship and also functions you used in your sample update statement. This was a very informative post. I could possible use this concept in many future data manipulation exercises.

I will need to wrap this update statement in a cursor because I do have 12K plus records to update and its taking a long time to run the entire statement. Or somehow I need to embed a commit somewhere in this update statement.

I really appreciate your help !!

Al

 

[Thargy]

Wrapping the statement in a cursor will slow it down.

Nothing goes faster than straight SQL, and that includes PL/SQL.

If the update of 12,000 records is taking too long, other methods are feasible.

How long is it taking to complete?
On what specification of machine?
Are you the only user, could you do the task when nobody else is around, and use all the CPU?

Regards

T

 

[taupirho]

If you run just this bit here

select x,y,dte,rowid rid,
lead over(partition by x order by dte desc) py
from tom

That gives you a result set that is the same as the table looks after the update so you have other options apart from an update.
If you are always updating the whole table you could try creatinga new table with the above select, drop the old table, and rename the new table to the old one, recreae indexes etc...

If you are updating a sub-set of the table, create a new table as above, delete the data you are updating in the old table and insert into the old table the data from the new table.

Could be worth a try.


In order to understand recursion, you must first understand recursion.

 

[AlStl]

Thargy,

Its a old Sun machine 5.6 with Oracle 10. It is way passed end of life.

I am not even sure how many other applications are using this machine.

I am not going to use cursor etc. My issue has been resolved based on solution suggested in taupirho last post.

So, thanks to both of you guys.

Al