Help: I have difficulties in Update Where Exists

  • Thread starter Thread starter Aldred@office
  • Start date Start date
A

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.
 
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:
Click to expand...

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
Click to expand...

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.
 
Back
Top