UPDATE statement for copying from one column to another in same table

I have a table which tracks how a task is moving from one approver's queue to another.I have these columns.
RTE_IDFR, RTE_SEQ_NBR , DATE_ASGN, RTE_ACTN_DATE, DATE_AVBL.
for1st aprover RTE_SEQ_NBR will be 1 for 2nd one 2 so on.RTE_IDFR, DATE_ASGN will be same for all approvers of one task.
I want to update date_avbl for 1st approver to be DATE_ASGN and for all subsequent approvers DATE_AVBL should be updated to RTE_ACTN_DATE of previous approver.I have correct RTE_ACTN_DATE for whole table but DATE_AVBL column is all messed up and hence i want this update.
Column descriptions :
 RTE_IDFR                                  NOT NULL NUMBER(10)
 RTE_SEQ_NBR                               NOT NULL NUMBER(5)
 DATE_ASGN                                 NOT NULL DATE
 RTE_ACTN_DATE                                      DATE
 DATE_AVBL                                          DATE
 
chsrinivas123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chsrinivas123Author Commented:
Detailed description of my question:
I have a table which tracks how a task is moving from one approver's queue to another.

I have these columns.
RTE_IDFR,
RTE_SEQ_NBR,
DATE_ASGN,
RTE_ACTN_DATE,
DATE_AVBL

For 1st approver RTE_SEQ_NBR will be 1
For 2nd approver RTE_SEQ_NBR will be 2
For 3rd approver RTE_SEQ_NBR will be 3 so on.

RTE_IDFR, DATE_ASGN will be same for all approvers of one task.

I WANT TO WRITE A SQL WHICH DOES MASS UPDATE:

I want to update DATE_AVBL for 1st approver to be DATE_ASGN of 1st approver and for all subsequent approvers DATE_AVBL should be updated to RTE_ACTN_DATE of previous approver.

REASON FOR DOING THIS WHICH YOU MIGHT NOT EVEN CARE: I have correct RTE_ACTN_DATE for whole table but DATE_AVBL column is all messed up and hence i want this update.

Column descriptions :
 RTE_IDFR                                  NOT NULL NUMBER(10)
 RTE_SEQ_NBR                               NOT NULL NUMBER(5)
 DATE_ASGN                                 NOT NULL DATE
 RTE_ACTN_DATE                                      DATE
 DATE_AVBL                                          DATE

ANY HELP WOULD BE REALLY APPRECIATED.
0
actonwangCommented:
Try this:

update
(select * from approve where rte_seq_nbr > 1 order by rte_seq_nbr desc) a
set date_avbl = (select b.rte_actn_date from approve b where b.rte_seq_nbr = a.rte_seq_nbr +1)
/
0
actonwangCommented:
Sorry, it should be:

update
(select * from approve where rte_seq_nbr > 1 order by rte_seq_nbr desc) a
set date_avbl = (select b.rte_actn_date from approve b where b.rte_seq_nbr = a.rte_seq_nbr -1 and b.rte_idfr = a.rte_idfr and b.date_asgn = a.date_asgn )
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

actonwangCommented:
I assume that your table name is "approve"
0
chsrinivas123Author Commented:
Hi  actonwang,
   That virtual table idea worked for me.Thanks a lot!!
0
actonwangCommented:
My pleasure :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.