Update Query

[Guest]

I need some advice on an update query please. I have a total of 7 tables, 6
of which need a field updated from the main table. The field in all 7 tables
is called the same: book_abbr. The main table is the table that holds the
updated abbreviation information, it is called Books. Do I need to create a
select query first then an update query to join the tables if they are not
joined? If so, can I join them by the field book_abbr even though this is not
the primary key in each of the 7 tables? The abbreviation to be updated may
only live in one of the 6 tables other tables or it may be in more than one.
I am not sure how the query looks at the old abbr in the table and know to
update it from the new abbr in the main table.

Any help is appreciated
Thanks

 

[Linda Burnside]

Try something like this:

UPDATE (((((Books INNER JOIN table1 ON Books.ID = table1.ID) INNER JOIN
table2 ON Books.ID = table2.ID) INNER JOIN table3 ON Books.ID = table3.ID)
INNER JOIN table4 ON Books.ID = table4.ID) INNER JOIN table5 ON Books.ID =
table5.ID) INNER JOIN table6 ON Books.ID = table6.ID SET table1.book_abbr =
[Books].[book_abbr], table2.book_abbr = [Books].[book_abbr],
table3.book_abbr = [Books].[book_abbr], table4.book_abbr =
[Books].[book_abbr], table5.book_abbr = [Books].[book_abbr],
table6.book_abbr = [Books].[book_abbr];


Linda

 

[Steve Schapel]

Leesa,

Is this a one-off requirement, or will this type of update be required
on a regular basis? I assume there is a field in the main table and
each of the other 6 tables which can be used to relate the tables
together? I think I would be inclined to do 6 separate Update Queries,
one at a time. The join on the tables would not be on the book_abbr
field, it would need to be on an ID field where the data is already
equivalent. Either that, or the main table would have to contain the
old book_abbr value as well as the new one. In either case, you can
then update the book_abbr field in the secondary table to the new
book_abbr field from the main table.

Having said this, I should also say that this is a very unusual
requirement, and should not really arise. It is a symptom of an
incorrect database design. Is there a possibility that you could review
and modify your table structure? If so, you will get good advice if you
would be interested to explore this aspect, either here or in the
microsoft.public.access.tablesdbdesign newsgroup.