Re: SQL Delete with JOIN

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.