Update statement- script timeout

[devi]

hi,

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.
:(

devi

[Anthony England]

"devi" <de******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

hi,

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.
:(

devi

A script timeout error? Are you doing this with an ASP page or something?
Can you not work directly with the mdb file on your pc?
While SQL statements generally give the best performance, I would guess that
it would be quicker to create a recordset and loop through all the records.
Each time you come to a new prn you blank out PreviousStatus and
PreviousAssignee and store the Status and Assignee. Moving to the next
record you see if it is the same prn and if so put your stored fields into
the current fields and grab the current values for Status and Assignee.
I would guess this would be quite quick, but how many records are we talking
about?

[Anthony England]

"devi" <de******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

hi,

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.
:(

devi

A script timeout error? Are you doing this with an ASP page or something?
Can you not work directly with the mdb file on your pc?
While SQL statements generally give the best performance, I would guess that
it would be quicker to create a recordset and loop through all the records.
Each time you come to a new prn you blank out PreviousStatus and
PreviousAssignee and store the Status and Assignee. Moving to the next
record you see if it is the same prn and if so put your stored fields into
the current fields and grab the current values for Status and Assignee.
I would guess this would be quite quick, but how many records are we talking
about?

[devi]

I am using ASP page.
Yes, I update Previous values as when i insert a new record in
History..

but the existing records..? so this query is to update the existing
History records with the previous values of status and assignee,

[Anthony England]

"devi" <de******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

I am using ASP page.
Yes, I update Previous values as when i insert a new record in
History..

but the existing records..? so this query is to update the existing
History records with the previous values of status and assignee,

As I understand it, you have made a design change to the database schema and
as a one-off change, you will have to change the table structure and
populate it with data. This is something you need to do once, not as a
regular routine, right?

Now although I didn't write the code for you, what I am suggesting you do is
not update via a single SQL statement, but instead create a recordset
variable and loop through it in the way I described. Do you understand what
I mean?

Also, as this is a big structural change, I would take the site down for
maintainence, download the mdb to your pc and do the update there rather
than trying to do the update via the ASP web page.

[Anthony England]

"devi" <de******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

I am using ASP page.
Yes, I update Previous values as when i insert a new record in
History..

but the existing records..? so this query is to update the existing
History records with the previous values of status and assignee,

As I understand it, you have made a design change to the database schema and
as a one-off change, you will have to change the table structure and
populate it with data. This is something you need to do once, not as a
regular routine, right?

Now although I didn't write the code for you, what I am suggesting you do is
not update via a single SQL statement, but instead create a recordset
variable and loop through it in the way I described. Do you understand what
I mean?

Also, as this is a big structural change, I would take the site down for
maintainence, download the mdb to your pc and do the update there rather
than trying to do the update via the ASP web page.