Updating a field on multiple criteria


R

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
 
Ad

Advertisements

G

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
 
Ad

Advertisements

J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top