How to quickly update all fields in a query?

[Sum]

Is there an efficient way to update all fields from one table to another table, when both tables have an identical structure?

I can quickly add all of the fields in the table to be updated, but how do I tell the query to update these fields from the update table without having to type each field in by hand?

Thanks

 

[plog]

This is the first small sign:

...update all fields from one table to another table...

This is the second bigger one:

...when both tables have an identical structure...

You most likely have a poorly structured database. Why do you have tables with the same structure? And why do you need to UPDATE query as opposed to a SELECT query?

 

[Sum]

I have a temporary table that I use to update a master table. I use both update and append queries to bring in information from the temporary table to the master table.

The structure works fine, I'm just wondering if there is an easier way to create an update query without needing to manually specify each field to be updated.

Any thoughts?

 

[plog]

You should be able to use the *

 

[Sum]

You should be able to use the *

Can you tell me how? I know how to use the * to add the fields to be updated, but how do you use it to update all of those fields with the appropriate fields from the source table?

 

[plog]

I'm sorry, you can't use the * for an UPDATE, only for APPEND.

 

[Sum]

No problem, does anyone else have a suggestion on how to efficiently write an update query so it updates all fields in one table with the same named fields in another table?

 

[Sum]

Let me ask a simpler question:

Is there a shortcut to specify the "Update To" field in an Update query without needing to manually type the table and field names? For example, I currently have to type the following in the "Update To field" for Zone:

[spupdate - Current SM-AM Spreadsheet].[Zone]

I can't drag Zone to the "Update To" field, and I can't click my cursor in the field and then double-click the Zone field in the source table. Is there any faster way rather than needing to type this out for every field needing to be updated?

 

[Brianwarnock]

Let me see if I've got this correct.
You have a master table and a temp table. The temp table contains records that effectively replace those in the master table based on some field probably an I'd being equal and append as to the master table new Ids.

If this is correct then how about running a delete query to delete from the master table where the ids are equal and then an append of all records from the temp table.

Brian

 

[boblarson]

Setting up the query ONCE manually in the QBE grid is what I'm seeing as the way to go. I don't understand why you are having such a hard time with that. USE THE GRID, it doesn't require typing and you can just drag and drop all of the fields on the grid and then select Append Query from the type and it will ask which table to append to and then all of the fields will match up if named the same.

 

[Brianwarnock]

Bob it's the update query he is having trouble with, you have to manually type in the update to field, mind you I still don't understand why it will be more than a one off, how many fields has he got?

Brian

 

[Sum]

Let me see if I've got this correct.
You have a master table and a temp table. The temp table contains records that effectively replace those in the master table based on some field probably an I'd being equal and append as to the master table new Ids.

If this is correct then how about running a delete query to delete from the master table where the ids are equal and then an append of all records from the temp table.

Brian

That's a good suggestion, but doesn't it defeat the purpose of an update query? I was hoping that I missed something obvious and there was an easier way to specify the fields to be updated in the query.

 

[Sum]

Bob it's the update query he is having trouble with, you have to manually type in the update to field, mind you I still don't understand why it will be more than a one off, how many fields has he got?

Brian

Exactly, append queries are a lot easier but for some reason Access requires you to type in each field name by hand for update queries.

Unfortunately, I have several update queries to maintain, containing around 40 fields each, and the queries change over time.

 

[boblarson]

Exactly, append queries are a lot easier but for some reason Access requires you to type in each field name by hand for update queries.

The reason is that it isn't necessarily field values that can go there and, in fact, that would be rare.

Unfortunately, I have several update queries to maintain, containing around 40 fields each, and the queries change over time.

Sounds like a situation where it could have been designed better. But, I have a question. Can you give an example of one of your update queries (actual SQL) and how it could change over time? I am thinking of a possible solution but need more info.

 

[Sum]

The update queries change over time as the tables being updated change (fields may be added or removed).

I think I found a decent solution. I can copy the source table name and paste it into the update to field for each field, then all I have to do is type the first couple letters of the field name. Intellisense will pull up the correct field and I can just tab to the next field.

Thanks for your help!

 

[boblarson]

as the tables being updated change (fields may be added or removed).

Just an FYI - I will state again that is a symptom of an improper design. You should almost never need to add or delete fields if designed properly. I say ALMOST because there can be situations that need that but the are extremely rare.

 

[Brianwarnock]

Ok so SUM is happy, and I agree with Bob about the design, but what i don't understand is what is wrong with the Delete matched and Append all solution. It works at the record level so changes to the table layout wouldn't effect it.

Aloso I would be interested in the performance of of delete/append versus updating 40 fields, would there be a difference and which would be better?

Brian

 

[Sum]

Just an FYI - I will state again that is a symptom of an improper design. You should almost never need to add or delete fields if designed properly. I say ALMOST because there can be situations that need that but the are extremely rare.

As business needs change, the required fields in a query can also change. There's nothing wrong with the design; the queries reflect the current needs of the business.

 

[Sum]

Ok so SUM is happy, and I agree with Bob about the design, but what i don't understand is what is wrong with the Delete matched and Append all solution. It works at the record level so changes to the table layout wouldn't effect it.

Aloso I would be interested in the performance of of delete/append versus updating 40 fields, would there be a difference and which would be better?

Brian

Nothing wrong with your suggestion, but it obviates the need for an update query at all. You could just delete and append every time something changed and never use an update query.

I'm not sure which is more efficient, but the query runs very quickly for me.

Thanks again for your help.

 

[Brianwarnock]

As business needs change, the required fields in a query can also change. There's nothing wrong with the design; the queries reflect the current needs of the business.

If designed properly and normalised the fields in a table, which is what we are talking about here, should rarely change.

Brian