Combine Update queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is it possible to combine update queries with their own WHERE conditions?
For example -- these 2 queries:

1. UPDATE HiPlanNames
SET WFFSEN = 0
WHERE (IND_ONLY=True AND FFSEN Is Null);

2. UPDATE HiPlanNames
SET WHMOEN = 0
WHERE (IND_ONLY=True AND HMOEN Is Null);
 
Luther

After saving a backup, ...!

What happens if you strip out all of the second query through "WHERE",
replace with "OR", and remove the semi-colon from the first SQL statement?
In other words, something like:

UPDATE ...
SET ...
WHERE ...
OR ...;

Regards

Jeff Boyce
<Office/Access MVP>
 
Thanks, Jeff; that worked out very well. May I venture to say that I can
string out a bunch of updates and match their "where" conditions like this:

UPDATE HiPlanNames
SET WFFSEN = 0, WHMOEN = 0
WHERE (IND_ONLY=True AND FFSEN Is Null)
OR (IND_ONLY=True AND HMOEN Is Null);
 
I'd be careful here.

You are going to update both fields any time either set of criteria is true. Is
that what you want? IF you only want to update the nulls then you will need to
use an immediate if statement in the Set clause.

UPDATE HiPlanNames
SET WFFSEN = IIF(FFSEN Is Null,0,WFFSEN),
WHMOEN = IIF(HMOEN is Null,0,WHMOEN)
WHERE IND_ONLY=True
AND (FFSEN Is Null OR HMOEN Is Null)
 
You're probably right. What I really want is this:

1. Update WFFSEN = 0 ONLY when (IND_ONLY=True AND FFSEN Is Null);
2. UPDATE WHMOEN = 0 ONLY when (IND_ONLY=True AND HMOEN Is Null);

Kind of goes back to the original queries....
 
That should be what my revisd query would do. Note the IIF in the Set clause.
It basically sets the value to zero or to itself when IndOnly is true. Need to
set the value to itself in the case that one field is null and the other is not
null.

You could, as you observed, go back to the original queries.
 
Back
Top