Re: Update TAble problem

On 14 Mar 2006 06:39:19 -0800, "devi" <devivelu_at_gmail.com> 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.
>

<snip>

Maybe you could take a different (and possbily quicker) approach by generating a new table and afterwards switching it with the old one, something like

CREATE TABLE history_new
AS
SELECT id,prn,status,assignee,
LAG(status) OVER (PARTITION BY prn ORDER BY id) previousstatus, LAG(assignee) OVER (PARTITION BY prn ORDER BY id) previousassignee

which will also throw in the NULL values for free.

Jaap. Received on Wed Mar 15 2006 - 04:41:10 CST