RE: record delete using a join

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Nazeer Oasis (NazeerOasis_at_discussions.microsoft.com)
Date: 10/30/04 

Date: Fri, 29 Oct 2004 21:21:01 -0700

Try this,

DELETE pjinvdet from PJINVDET
          JOIN pjpent ON
        pjinvdet.project = pjpent.project
Where pjinvdet.project_billwith = '0278602' and
        pjinvdet.pjt_entity = pjpent.pjt_entity and
        (pjpent.contract_type = '4' or pjpent.contract_type = '5') and
        pjinvdet.bill_status = 'U' and
        pjinvdet.draft_num = '' and
        (pjinvdet.acct_rev = 'UNBILLED FEES' or
         pjinvdet.acct_rev = 'UNBILLED CONSULT' or
         pjinvdet.acct_rev = 'PROF FEES REV' or
         pjinvdet.acct_rev = 'CONSUL REV')

---------------
Nazeer
Dimensions India
---------------
"bc" wrote:

> The following code works fine to select, but derails at line 3 (the JOIN) with a syntax error if run as a deleting statement. Can anybody tell me why? I know I'm going to feel dumb when I get the answer....
>
> Select pjinvdet.*
> -- DELETE
> From pjinvdet
> JOIN pjpent ON
> pjinvdet.project = pjpent.project
> Where pjinvdet.project_billwith = '0278602' and
> pjinvdet.pjt_entity = pjpent.pjt_entity and
> (pjpent.contract_type = '4' or pjpent.contract_type = '5') and
> pjinvdet.bill_status = 'U' and
> pjinvdet.draft_num = '' and
> (pjinvdet.acct_rev = 'UNBILLED FEES' or
> pjinvdet.acct_rev = 'UNBILLED CONSULT' or
> pjinvdet.acct_rev = 'PROF FEES REV' or
> pjinvdet.acct_rev = 'CONSUL REV')
>
>
> User submitted from AEWNET (http://www.aewnet.com/)
>