Updating a field on multiple criteria

[Ramesh]

Hi,

Is there a way i cud use a single update query to update a status field
based on the contents of different fields? Like update the status field
with value A if field X is blank, value B if field Y is blank, or value C if
field Z contains abc.

currently i use multiple queries, one query for each crieria, to be run one
after the other. wud be great if they cud all be put into one single query.
or atleast a single operation which will run all the queries (like a batch
file).

Thanks for any inputs.
Ramesh

 

[Guest]

In SQL you can build queries that contain multiple WHERE criteria. So yes
you should be able to build a single update query which would take into
consideration all of you criteria. What I would suggest for learning
purposes would be to use the copy your first query under a new name and add
each of the criteria then switch into SQL view to see the actuall SQL synthax.

Daniel

 

[John W. Vinson]

Hi,

Is there a way i cud use a single update query to update a status field
based on the contents of different fields? Like update the status field
with value A if field X is blank, value B if field Y is blank, or value C if
field Z contains abc.

currently i use multiple queries, one query for each crieria, to be run one
after the other. wud be great if they cud all be put into one single query.
or atleast a single operation which will run all the queries (like a batch
file).

You can use the Switch() function to dynamically calculate the
update-to value:

UPDATE mytable
SET Status = Switch([X] IS NULL, "A", [Y] IS NULL, "B", [Z] = "abc",
"C", True, [Status])
WHERE [X] IS NULL OR [Y] IS NULL OR [Z] = "abc";

The Switch() function takes arguments in pairs and reads them
left-to-right; it will stop when it first encounters a pair where the
first argument is TRUE and return the second argument.

One question: what is the appropriate STATUS value if X and Y are both
blank? or if all three conditions apply?

John W. Vinson [MVP]