Help With Update Query

[Smartin]

Firstly, please excuse the multipost. I have been unable to get
solutions in comp.databases.ms-access.

Can anyone help me get this update query right?

The SELECT subquery does select the rows I want to update when taken on
its own.

However when I add the UPDATE piece it finds no rows to update. What am
I missing here...

===== begin SQL
UPDATE Claims SET Payment = 'Not Required'
WHERE Payment IN
(
SELECT Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND Canceled = False
AND IsNull (Payment)
);
===== end SQL

I have been offered using aliases and adding parens in the subquery
WHERE constraints, but neither affects the result.

Thanks for all help!

 

[John Vinson]

Firstly, please excuse the multipost. I have been unable to get
solutions in comp.databases.ms-access.

Can anyone help me get this update query right?

The SELECT subquery does select the rows I want to update when taken on
its own.

However when I add the UPDATE piece it finds no rows to update. What am
I missing here...

===== begin SQL
UPDATE Claims SET Payment = 'Not Required'
WHERE Payment IN
(
SELECT Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND Canceled = False
AND IsNull (Payment)
);
===== end SQL

Well, as written, the Subquery will select only NULL values of
Payment, since you're explicitly using IsNull(Payment); you'll then
set the value of Payment in Claims for those values where Payment IN
(NULL). I'm guessing that this will return no records. I'd also expect
the Subquery to return the value of PeopleID rather than the value of
Payment - if Payment is a number, wouldn't this delete all records
with a null Payment, whoever they're from?

Could you explain in words which records should be updated? Which
fields are in Payment and which in Claims? Does it help to explicitly
identify the field Payment in the subquery?


John W. Vinson[MVP]

 

[Smartin]

Well, as written, the Subquery will select only NULL values of
Payment, since you're explicitly using IsNull(Payment); you'll then
set the value of Payment in Claims for those values where Payment IN
(NULL). I'm guessing that this will return no records. I'd also expect
the Subquery to return the value of PeopleID rather than the value of
Payment - if Payment is a number, wouldn't this delete all records
with a null Payment, whoever they're from?

I'm not following you here. The subquery alone returns Payment of the
expected records (Payment is a string representing status, not a
number). When I wrap the Update around the subquery suddenly nothing is
selected.

Why do you say PeopleID would be returned by the subquery?

Could you explain in words which records should be updated? Which
fields are in Payment and which in Claims? Does it help to explicitly
identify the field Payment in the subquery?

John W. Vinson[MVP]

I am trying to update Claims where Payment is Null AND the parent record
has People.Foo = People.Bar. I've revised the query to show all table
references explicitly (it doesn't work this way either):

===== begin SQL
UPDATE Claims SET Claims.Payment = 'Not Required'
WHERE Claims.Payment IN
(
SELECT Claims.Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND People.Canceled = False
AND IsNull (Claims.Payment)
);
===== end SQL

FWIW I also tried moving 'AND IsNull (Claims.Payment)' to the Update's
WHERE clause with no effect.

Thanks for your assistance!