New lookup table and insert statement

[ekta22]

We have a table, Region, that has the following fields

Region Sector DistrictOffice
EA------11------

http://WWW00(this

value needs to be filled)
EA------22------

http://WWW00

EA------33------

http://WWW00

AL------11------

http://WWW11

AL------22------

http://WWW11

They have asked us to create a new field, District Office, where all EA 11, EA 22 and EA 33 will fall under one category say

http://WWW00

and AL 11 and AL 22 will fall under

http://WWW11

and so on. I created a new table with this new field and also added this field in the Region table and linked it with one-to-many relationship.

My question is.. there are already thousands of records in Region table and I need to add data in this new field based on the above criteria. What would be the best way to do it and the syntax?

Any help is really appreciated.

Thanks

 

[lespaul]

So originally you had a table REGIONS with a structure of:

Region Sector
EA 11
EA 22
EA 33
AL 11
AL 22

Now for each region, you also need to know the DistrictOffice? So you modified the above table to include the DistrictOffice field:

Region Sector DistrictOffice
EA 11
EA 22
EA 33
AL 11
AL 22

and now you need to fill in the DistrictOffice for each record?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ekta22]

Yes, you are right. A lot of regions can fall under a single District Office and that is why I created another table with all the District Offices and added it in Region table as a foreign key. Question is how to update the District Office field in the Region table with the data that will come from the new District Office table.

 

[lespaul]

If you've added it as a foreign key to the Region table, you don't need to do anything except join into the new table:

FROM Region
INNER JOIN NewTable On Regions.ForiegnKeyAdded = NewTable.FieldInRegions



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi