Re: Update TAble problem

devi wrote:
> 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?
>
> :(

An Oracle version number and an Explain Plan would be useful.

What bothers me most about what I see is the use of a column named "ID". You might want to keep this query handy.

SELECT keyword
FROM v$reserved_words;

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)