Update Query Question/Advice (1 Viewer)

Hello all,

I have a single table to which I'd like to update certain records.

I'd like to update certain fields for these certain records.

For example I'd like to update the 'DataClassification' for Record ID 001 to "Public" but then I'd like to update the DataClassification for Record ID 002 to "Internal". I'd then like to update the 'Status' of Record ID 003 to "Live" but not update the Dataclassification for Record ID 003. Is it possible to do this in one query? I suspect it is by adding the fields then using the record ID's as the criteria.
Ideally what I'd like to do is to have a second table where I could list all of these 'exceptions' and then have the update query work from there.

For example:

RecordID | FieldToChange | UpdateTo
---------------------------------------
001 | DataClassification | Public
002 | DataClassification | Internal
003 | Status | Live

I'm not currently understanding how to do that or if it is even possible?

I wouldn't try to do it from one query
Instead I would create a function to run each of these update queries in succession.
However, if these are just a sample of your 'exceptions', using a reference table is probably a good idea

Thanks Ridders!

How would I use a reference table in an update query?

Depends on how your lookup preference table is structured.

You could use a series of DLookups but if you have lots of records that will be very slow.
A much better approach is to add both tables to your update query, link by the field being compared (RecordID) and set the other field(s) to the corresponding field value in the lookup table

It is unusual to create update queries that change specific values based on a specific primary key value. Queries are set operations. They are intended to do the same thing to all records of a set. Of course, there might be only a single row in a set as it is in your case but you have three discrete sets that seem to be not in any way related. Will you want to update several different records tomorrow with different values? I would not create queries to do this. I would simply use the interface for one off updates. That way all your validation code also runs ensuring that the resulting record is valid (you do have validation code don't you?).

Sometimes you get a batch of updates from a different source. Perhaps you create a spreadsheet for each department and ask them to update their contact information. In that case, you wouldn't want to do the updates one at a time. For these bulk updates, import the updates into a table. Join on the primary key and for each field that it is possible to update just map the columns.