If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Help: I have difficulties in Update Where Exists
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] |
#3
|
|||
|
|||
回覆: Help: I have difficulties in Update Where Exists
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] |
Thread Tools | |
Display Modes | |
|
|