Update TAble problem

hi all,

I am creating a sample bug tracker (using Oracle 10g db) and i created a hisotry table to
log the bug history.

The history table contains details like ProblemRecordNo (PRN), RecordStatus, Assignee, Reporter.
The PK for this table is ID.
The history table contains multiple entries for a Record.

I have inserted a lot of records int History table and now I introduced two new fields into History table. They are PreviousStatus and PreviousAssignee. I wanted to update the previous values of the Status and Assignee for each record.
Say my History table contains values like

ID  PRN   Status       Assignee
1    10    Report         UserA
2    10    InProcess     UserA
3    10    Esclated       UserB

Now after introducing the Previous fields, the History table should look like

ID  PRN   Status       Assignee   PreviousStatus   PreviousAssignee
1    10    Report         UserA
2    10    InProcess     UserA          Report              UserA
3    10    Esclated       UserB         InProcess           UserA

In the first record the PreviousStatus and PreviousAssignee are empty bse there is no previous values for those two items. the next two records contain the previous values of the status and assignee.

I used the following sql statement to update the table, but I get a "cannot modify a column which maps to a non key-preserved table" error.

UPDATE (
SELECT H.PRN, H.ID, H.PREVIOUSSTATUS AS OLDSTATUS, N.STATUS AS NEWSTATUS,
H.PREVIOUSASSIGNEE AS OLDASSIGNEE, N.ASSIGNEE AS NEWASSIGNEE FROM HISTORY H,
(SELECT N.ID, N.PRN, N.STATUS, N.ASSIGNEE FROM HISTORY N ) N
WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN
(SELECT MAX(M.ID) FROM HISTORY M WHERE M.ID < H.ID AND H.PRN = M.PRN) )
SET OLDSTATUS = NEWSTATUS,
OLDASSIGNEE = NEWASSIGNEE So I used a different sql to update the table like

 UPDATE History H SET (H.PreviousStatus, H.PreviousAssignee) =    ( SELECT N.Status, N.Assignee FROM History N      WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN     (SELECT MAX(M.ID) FROM History M WHERE M.ID < H.ID     AND H.PRN = M.PRN) ) But this sql query takes a long time to update records (say 18000 records)

Is there anyother way i could update the table with a optimized query that doesnt take time to update large set of rows.

Any help?

:( Received on Tue Mar 14 2006 - 08:39:19 CST