Posted by devi on 10/01/18 11:42

Sorry. I guess I wasnt clear in explaning my doubt...

I am creating a simple bug tracker application (in Access db) and i
created a hisotry table to
log the bug history.

The history table contains details like ProblemRecordNo (PRN),
RecordStatus, Assignee, Reporter (and also some more columns). The PK
for this table is ID. It 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
script time out 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

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

:(

[Back to original message]