Update query

[scotty22]

Hi,



I am trying to create an update query that will read an ID from one table [Updates], and update it in another, [Original]

[Updates] has the following structure:

ID FieldToAmend Change To

[Original] has this structure...

ID Field1 Field2 Field3 ...Field 70

I create a query to update one field at a time, but I obvioulsy would like to update them altogether. Is this possible?

Many thanks,

Scotty22

[orange]

You really don't have a table with field names field1 thru field70, do you?

If you really do, then before dealing with the UPDATE query, I think you should do some investigation/research on Normalization and Database design.

Have you tried to google Update query?

[scotty22]

The fields are not actually called Field1, field2 etc but yes there are around 70 of them.

I have google searched update query, but I can't seem to find some SQL code that is suited to my problem. With regards to normalistaion, I don't think there is any way round me having 70 fields because they provide unique information for each record.

I think my main problem is trying to pass a value as a field, which I don't think I can do in SQL.


Any further thoughts?

[orange]

Here's a link to syntax for Update
http://www.techonthenet.com/sql/update.php

It is rare to see a table with 70 fields. I'm not saying never, but I've been at this for a long time and I haven't seen a normalized structure with 70 fields (maybe 35-40).

If you can use some sample data or show a record or 2 that are typical of what you are trying to do, then maybe someone can help.

[scotty22]

I can seem to update one column, but I need to be able to update multiple columns at once.

So I need the query to look in the table [Updates] and for each record return the field to be updated, which is a value in the column [FieldToAmend], and also the value that it changes to, from the field [Change_To]. Then I need this particular record's field updated in the table [Original].

After a look round the internet, I've come across an Exec function that might be able to do the trick, but I can't seem to get my head around it.

Any comments?

[orange]

An Update query can affect many records in your table, so understanding what it does and how to constrain the updates to certain records is absolutely critical.

DO NOT USE UPDATE UNTIL you backup your data
and understand what the Update query does and how.

Might do the trick is absolutely the wrong way ro approach UPDATE query.

I would not suggest that you use some exec you found.