Help: I have difficulties in Update Where Exists

[Aldred@office]

Hi all,
I have a query which is exactly like this:

Update tDelivered Set InvoiceID = 999 where Exists (Select * from
tClient INNER JOIN (tPOin INNER JOIN (tPOItems INNER JOIN tDelivered
ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID) ON
tPOin.ClientID=tClient.ID
WHERE Wanted = true and InvoiceID is NULL AND EName =
Forms!fGenInvoice.EName and InvoiceID is NULL Order by Ddate)


Access 2007 tries to update all the records in tDelivered. However, when I
dod the Select statement in the Exists above like this:


Select * from tClient INNER JOIN (tPOin INNER JOIN (tPOItems INNER JOIN
tDelivered ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID)
ON tPOin.ClientID=tClient.ID
WHERE Wanted = true and InvoiceID is NULL AND EName =
Forms!fGenInvoice.EName and InvoiceID is NULL Order by Ddate

It returns 5 records which are what I expected to see.

Can someone please point out my mistake?

Thanks.

 

[John W. Vinson]

Hi all,
I have a query which is exactly like this:

Update tDelivered Set InvoiceID = 999 where Exists (Select * from
tClient INNER JOIN (tPOin INNER JOIN (tPOItems INNER JOIN tDelivered
ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID) ON
tPOin.ClientID=tClient.ID
WHERE Wanted = true and InvoiceID is NULL AND EName =
Forms!fGenInvoice.EName and InvoiceID is NULL Order by Ddate)


Access 2007 tries to update all the records in tDelivered. However, when I
dod the Select statement in the Exists above like this:

That's what I would expect. The EXISTS clause is true - and will cause the
record to be updated - if there exists *any* record in the entire table which
meets that criterion.

Select * from tClient INNER JOIN (tPOin INNER JOIN (tPOItems INNER JOIN
tDelivered ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID)
ON tPOin.ClientID=tClient.ID
WHERE Wanted = true and InvoiceID is NULL AND EName =
Forms!fGenInvoice.EName and InvoiceID is NULL Order by Ddate

I think you want to INNER JOIN this query to tDelivered, or use a correlated
subquery. I'm not certain what the intended logic might be.

 

[Aldred@office]

Thanks. Now at least I know that the update will run whenever exists is
true.