Update query

E

Ed

Hi:
I have a bunch of tables in which I'd like to globally
update the BLDG_NO field. The data in this field does not
coincide with the data in the BLDGMaster table. For
example: BLDG 0001 Should be 1001
0002 Should be 1213 etc...
Is there an easy way to update the field so that all
other related tables are updated automatically. The
BLDG_NO field is not the primary key. I have other fields
such as REGION_NO and DISTRICT_NO that must be updated
this same way as well. Any input would be greatly
appreciated. Thanks
Ed Dobbin
 
K

Ken Snell [MVP]

I'd recommend that you create a table with two fields in it. The first field
should contain the value that is currently in the permanent table -- the
value you want to replace. The other field should contain the new value --
the value that will replace the current value.

Then run an update query that is joined to this table to get the new value.
Here is a generic example of what that query would be:

UPDATE PermanentTableName
INNER JOIN CreatedTableName
ON PermanentTableName.BLDG_NO =
CreatedTableName.BLDG_NO
SET PermanentTableName.BLGD_NO =
CreatedTableName.NewBLDG_NO;

Repeat the process for the other fields.
 
E

Ed

Thank you so much Ken.
I did as you said and it updated my tables just the way I
wanted. Thank you, you are the greatest!
Ed
 
K

Ken Snell [MVP]

By the way, you may want to rethink how your tables are structured. If
BLDG_NO is not a primary key, it should be stored in just one table and then
referenced by other tables through its table's primary key.

Good luck.
 
E

Ed

Yes, that's the way the tables are set up. For old
dBaseIV tables they are structured well. Thanks Ken I'm
writing a preventive maintenance program for government
buildings with this database. I appreciate your help.
Ed
 
Top