request for help with a SQL update query

[Paul James]

I'm trying to create an update query that will set the value of a Yes/No
field to -1 when two fields in all the corresponding records in a related
(many) table are not null.

The field to update is named "Processed," and is in a table named
tblInvoice.
The related (many) table is tblDetail, and the fields which must not be null
are named "PCA" and "Source."

The statement I've tried to use is:

UPDATE tblInvoice SET Processed = -1 WHERE Is Not Null ALL (SELECT D1.PCA,
D1.Source FROM tblDetail AS D1 WHERE D1.Invoice_ID = I1.Invoice)

However, I'm getting a syntax error when I try to run it. The message
identifies the error as beginning with

"Is Not Null All (SELECT . . .

and continuing to the end of the statement.

Can anyone tell me how to modify this statement to get it to work?

Thanks in advance,

Paul

 

[Gerald Stanley]

Try something along the lines of

UPDATE tblInvoice AS I INNER JOIN tblDetail AS D ON
I.invoiceId = D.invoiceId
SET I.processed = -1
WHERE D.pca IS NULL
AND D.source IS NULL

Hope This Helps
Gerald Stanley MCSD

 

[Paul James]

It's perfect!

Thank you so much, Gerald.

Paul

 

[John Spencer (MVP)]

Since there may be multiple records in the detail and one PCA might be null or
one Source might be null then I would try something like the following.

UPDATE tblInvoice As I
SET I.Processed = -1
WHERE 0 = (SELECT Count (*)
FROM tblDetail AS D1
WHERE D1.Invoice_ID = I.Invoice
AND (D1.PCA Is Null Or D1.Source Is Null))

This should only set Processed to -1 (True) when there are no nulls in any of
the two fields in any of the related records.

TEST this on a copy of your data to make sure it does what you want.


Another way to achieve your goal might be two queries. First query sets ANY of
the tblInvoice.Processed fields to TRUE (-1) that aren't true.

UPDATE TblInvoice
SET TblInvoice.Processed = -1
WHERE tblInvoice.Processed <> -1

The second one then Sets any of the tblInvoice.Processed to False that have one
or more nulls in tblDetails.

UPDATE tblInvoice As I INNER JOIN TblDetail As D
ON I.Invoice_ID = D.InvoiceID
SET I.Processed = -1
WHERE D1.PCA Is Null Or D1.Source Is Null

 

[Paul James]

Many thanks to both Gerald and John for their code.

John, you are right - I needed to modify the query as you suggested to get
it to work. The previous code was marking the Invoices as processed if ANY
of the (multiple) related records in tblDetail had values in both fields.
Even if other related records had Null values, it was still marking the
Invoice record as "Processed." That of course wasn't what I needed. I
hadn't checked the code carefully enough.

After correcting one or two minor typos in the code you gave me, I found
that both of your solutions worked, and both produce the same results. The
single query that works is:

currentdb.execute "UPDATE tblInvoice As I SET I.Processed = -1 WHERE 0 =
(SELECT Count(*) FROM tblDetail AS D1 WHERE D1.Invoice_ID = I.InvoiceID AND
(D1.PCA Is Null Or D1.Source Is Null))"

And the other pair of queries work as well. The first one results in all
Processed fields having the value of True (-1):
currentdb.execute "UPDATE TblInvoice SET TblInvoice.Processed = -1 WHERE
tblInvoice.Processed <> -1"

And the second one sets the Processed field to false (0) if there is a
single record in tblDetail that has a Null value in either PCA or Source:
currentdb.execute "UPDATE tblInvoice As I INNER JOIN tblDetail As D ON
I.InvoiceID = D.Invoice_ID SET I.Processed = 0 WHERE D.PCA Is Null Or
D.Source Is Null"

I did notice one feature of both solutions which is probably not a problem,
but it would be nice if there was a way to eliminate it. If there are any
records in tblInvoice that have no corresponding record in tblDetail, the
"Processed" field will be set to -1. The reason I say this isn't a problem
is that in the real world of the production database, there aren't any
meaningful Invoice records that have no corresponding detail records.
(tblDetail is where the Description and Amount fields are entered, and an
Invoice without a Description and Amount isn't meaningful). Nevertheless,
from a database design standpoint, it would be preferable to not mark the
invoice record as processed if it has not detail table, or to run another
SQL statement that would set the value of the Processed field to 0 for all
records in tblInvoice that have not corresponding records in tblDetail.

Can either of you suggest a way to do this?

And thanks again to both of you.

Paul

 

[John Spencer (MVP)]

Then you would need to check and see if there were any records at all in
tblDetails. The following UNTESTED SQL might work, since you should not see any
TblInvoice returned if there is no tblDetail record. It might fail, if so try
adding DISTINCTROW to the Update clause (UPDATE DistinctRow ...).


UPDATE tblInvoice As I INNER JOIN TblDetail
ON I.Invoice_ID = TblDetail_ID
SET I.Processed = -1
WHERE 0 = (SELECT Count (*)
FROM tblDetail AS D1
WHERE D1.Invoice_ID = I.Invoice
AND (D1.PCA Is Null Or D1.Source Is Null))

 

[John Spencer (MVP)]

Then you would need to check and see if there were any records at all in
tblDetails. The following UNTESTED SQL might work, since you should not see any
TblInvoice returned if there is no tblDetail record. It might fail, if so try
adding DISTINCTROW to the Update clause (UPDATE DistinctRow ...).


UPDATE tblInvoice As I INNER JOIN TblDetail
ON I.Invoice_ID = TblDetail_ID
SET I.Processed = -1
WHERE 0 = (SELECT Count (*)
FROM tblDetail AS D1
WHERE D1.Invoice_ID = I.Invoice
AND (D1.PCA Is Null Or D1.Source Is Null))

 

[John Spencer (MVP)]

Then you would need to check and see if there were any records at all in
tblDetails. The following UNTESTED SQL might work, since you should not see any
TblInvoice returned if there is no tblDetail record. It might fail, if so try
adding DISTINCTROW to the Update clause (UPDATE DistinctRow ...).


UPDATE tblInvoice As I INNER JOIN TblDetail
ON I.Invoice_ID = TblDetail_ID
SET I.Processed = -1
WHERE 0 = (SELECT Count (*)
FROM tblDetail AS D1
WHERE D1.Invoice_ID = I.Invoice
AND (D1.PCA Is Null Or D1.Source Is Null))

 

[Paul James]

I ran the following code:

currentdb.execute "UPDATE tblInvoice As I INNER JOIN tblDetail ON
I.InvoiceID = tblDetail_ID SET I.Processed = -1 WHERE 0 = (SELECT Count(*)
FROM tblDetail AS D1 WHERE D1.Invoice_ID = I.InvoiceID AND (D1.PCA Is Null
Or D1.Source Is Null))"

but I got a "Join expression not supported" message.

Any suggestions for overcoming this?

(and thank you)

 

[Paul James]

I removed the typos and ran this:

currentdb.execute "UPDATE tblInvoice As I INNER JOIN tblDetail as D ON
I.InvoiceID = D.Invoice_ID SET I.Processed = -1 WHERE 0 = (SELECT Count(*)
FROM tblDetail WHERE D.Invoice_ID = I.InvoiceID AND (D.PCA Is Null Or
D.Source Is Null))"

Now it runs, without any error message, but it's back to marking as
"Processed" all Invoice records that have any related records meeting the
null criteria. But I need ALL the related records to meet the null criteria
before I consider the Invoice as "Processed."

???

 

[John Spencer (MVP)]

Hopefully this will get rid of all the typos and misreferences and more
importantly will actually work.

UPDATE tblInvoice As I INNER JOIN tblDetail as D
ON I.InvoiceID = D.Invoice_ID
SET I.Processed = -1
WHERE 0 = (SELECT Count(*)
FROM tblDetail AS S
WHERE S.Invoice_ID = I.InvoiceID
AND (S.PCA Is Null Or
S.Source Is Null))

 

[Paul James]

It works, John, and this time it doesn't set Processed to TRUE when an
invoice has no corresponding records in tblDetail.

Thanks so much for providing all these SQL statements, and for giving me the
solution I need.

Question: I'm trying to understand how your statement works. Here's the
complete statement:

UPDATE tblInvoice As I INNER JOIN tblDetail as D
ON I.InvoiceID = D.Invoice_ID
SET I.Processed = -1
WHERE 0 = (SELECT Count(*)
FROM tblDetail AS S
WHERE S.Invoice_ID = I.InvoiceID
AND (S.PCA Is Null Or
S.Source Is Null))

The part I'm not sure about is how the beginning of the WHERE clause works:

WHERE 0 = (SELECT Count(*) FROM tblDetail

in particular the 0 = (Select Count(*) portion.

What is that saying, and how does it manage to define (correctly) which
Invoice records should be marked as Processed?

Paul

 

[John Spencer (MVP)]

You want me to explain it!!! Don't you know that a magician never reveals his
secrets???? ;-)

WHERE 0 = (...)

We want no records in tblDetail to have a PCA or Source that is null. So we
count them and compare the count to zero.

We count them using a subquery that says tell me how many (count) records in
tblDetail (which we have aliased as a new table - the as S part) exist that have
an Invoice_ID equal to the main query's tblInvoice.InvoiceID for the row we are
currently processing in the main query (where we aliased tblInvoice as I).

The subquery in this example must return only one column and one row since we
are using the equal sign.

Hope that is clear. If not, post back and I'll try to come up with a more lucid expression.

 

[Paul James]

Hope that is clear. If not, post back and I'll try to come up with a more
lucid expression.

I don't know how you could make the explanation any more lucid than you just
did. Thanks so much.

Still another question: I noticed that you gave tblDetail one alias in the
INNER JOIN clause, ("D") and a different alias in the second WHERE clause
("S"). Did you do that purposely to avoid confusing the SQL statement, or
could you have used the same alias in both clauses?

 

[John Spencer (MVP)]

I did it on purpose to make sure that the SQL statement did not confuse things.
I think that it would not have worked correctly using the same alias.

If you wish, you can test it on a copy of your database and see what happens.

 

[Paul James]

Even when I use "D" as the alias for both clauses, the query works just
fine. So evidently it doesn't get confused when you use the same alias in
those two places. Maybe it's better, though, to use two different aliases
as a general rule and thus remove any chance of an ambiguity.

Thanks again, J.