Help: I have difficulties in Update Where Exists

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.

On Mon, 22 Feb 2010 12:19:46 +0800, "Aldred@office" aldred wrote:

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.


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

Can someone please point out my mistake?

Thanks.
--

John W. Vinson [MVP]

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

"John W. Vinson" 在郵件
ä¸*撰寫...
On Mon, 22 Feb 2010 12:19:46 +0800, "Aldred@office" aldred wrote:

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.


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

Can someone please point out my mistake?

Thanks.
--

John W. Vinson [MVP]