Update Query Question (1 Viewer)

[wem3rd]

I messed up my main table and need to update the values in one field back to the original copy. The backup is identical save original data in the messed up field.

 

[The_Doc_Man]

How many fields? How many records?

 

[wem3rd]

One Field with some 1800 records. Both table have identical primary keys.

 

[wem3rd]

I am a bit gun shy after botching my update efforts. The table has 11 fields, one of which is incorrectly updated.

Thank you for any tips that will help me restore the original values.

 

[The_Doc_Man]

Presuming you have a usable backup, you might be able to import that table from your backup copy to a WORKING copy (which will lead to having a total of 3 copies involved, and DON'T TOUCH THE ORIGINAL until this is done.)

Import the "clean" version of the table from your backup to a new table (different name) in the working copy. This step can be done from the External Data tab. The dialog will ask for a new table name, and to be honest, the name is immaterial. Just pick a name. At this point you will have the clean and dirty table copies side-by-side in the working database file.

Now manually create a query that looks like the UPDATE query shown next, substituting the correct table and field names, using the query builder in SQL mode. I've spaced it out a bit for readability.

UPDATE MainTable INNER JOIN CopiedTable 
    ON MainTable.Primekeyname = CopiedTable.Primekeyname
        SET MainTable.ScrewedupField = CopiedTable.ScrewedupField ;

Once you run that update, open the table in Datasheet View and CHECK the contents. Do NOT assume my instructions were perfect or that your implementation was perfect. If it doesn't look right, then either you did something wrong OR your description to me was misleading. But since you are working on a copy, you can delete the copy and start over again.

IF the updated table looks good, then delete the arbitrarily named table you copied from your backup copy. (Delete from the working copy.)

Then do a compact & repair on your working database.

Then rename the original DB file to some other name, perhaps by changing it from Name.MDB to Name_Bkup.MDB. Or something simple. You will want to keep this copy around for a couple of days just in case.

Then rename the working copy to the original name.

Do this carefully, one step at a time. When the renaming is done, try to use it and see if the updates make sense.

 

[wem3rd]

Thank you Doc_Man!

I will be super careful with this, and will let you know if I succeeded.

wem3rd

 

[wem3rd]

Thank you once more - all went smoothly this time around!

wem3rd

 

[The_Doc_Man]

Great! Glad to help.