Re: SQL Delete with JOIN

[Charles Wilt <charles.wilt@xxxxxxxxx>]

1) Because IBM follows the SQL standards...

2) Because you didn't use correlation, your WHERE EXISTS is always true...

delete from IPAPDTL D
where exists (select * from IPAPHDR H
              where H.AAVN=D.DAVN AND H.ATYP=D.DTYP AND H.AINV=D.DINV AND
                             H.AIDI=D.DIDI AND H.AGS$=D.DGS$ AND H.ADLP =
121213 )


Good tip: always to a SELECT prior to DELETE to make sure your deleting
what you want to :)

This may also work (and maybe be faster)
delete from ipapdtl
where (davn, dtyp, dinv, didi, dgs$)
  in (select aavn, atyp, ainv, aidi, ags$
       from ipaphdr
       where adlp = 121213)

HTH,
Charles


On Fri, Dec 14, 2012 at 9:54 AM, Glenn Gundermann <ggundermann@xxxxxx>wrote:

> Hi all,
>
> Normally I would ask an SQL person at work to write any complex SQL for me
> but I am trying this myself.  Mistake #1.
>
> I am trying to delete A/P details where the A/P header has a last payment
> date of 121213 (yesterday).
>
> Two things:
>
> 1.  Why does the IBM i not allow the same syntax as what I've found on the
> Net for other platforms? Is it the i or the other platforms that aren't
> standard?  eg. I'm getting the error message "Keyword FROM not expected.
> Valid tokens: USE SKIP WAIT WITH WHERE." for the following statement:
> DELETE D
> FROM IPAPDTL D
> INNER JOIN IPAPHDR H ON
>   H.AAVN=D.DAVN AND H.ATYP=D.DTYP AND H.AINV=D.DINV AND
>   H.AIDI=D.DIDI AND H.AGS$=D.DGS$
> WHERE H.ADLP = 121213
>
> 2.  Why did the following statement delete all my records?!
> DELETE FROM IPAPDTL
> WHERE EXISTS (
>   SELECT D.*
>   FROM IPAPDTL D, IPAPHDR H
>   WHERE H.AAVN=D.DAVN AND H.ATYP=D.DTYP AND H.AINV=D.DINV AND
>   H.AIDI=D.DIDI AND H.AGS$=D.DGS$ AND H.ADLP = 121213 )
>
>
> Yours truly,
>
> Glenn Gundermann
> ggundermann@xxxxxx
> Cell: 416-317-3144
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.