Modifying a table with no index but sequence only

[joshuaguilty]

I have a table "tableA" with no index, BUT only sequence.
Field1 Field2 Field3
1/1/2005 AAA 10
1/1/2005 BBB 20
1/1/2005 AAA 13
1/1/2005 BBB 30
1/1/2005 AAA 20
1/1/2005 CCC 50

This table is very busy therefore there is no way to stop it in production.

I would like to change some of the history of that table. Like line 3 on 1/1/2005 for "AAA" instead of 13 I want to change to 31.

My question is I want to get the line number of that table and then copy only all the lines related to "AAA" to a tableB. Therefore after modifying tableB, I match the lines and replace Field3 with the tableB new data.

Any idea? Thank you.

 

[alvechurchdata]

You could create a new field in the new table to hold the original record number:

SELECT RecNo() as Rec, TableA.* ;
FROM TableA ;
INTO Table TableB ;
WHERE Field3 = "AAA"

When you come to replace the updated values you can use the Rec field to select which records to update.

Why can't you just modify the live data? You say that the table's in constant use is there not a danger that another user will alter some of the "AAA" records whilst you're working with them?

If you were to set up a view into the table then you could work on a buffered copy of the live data and set the View options to prevent this sort of conflict.

Geoff Franklin

http://www.alvechurchdata.co.uk