Glen,
1. I don't know, but would like to
2. I think because the sql in the exists returns at least 1 row
What you failed to do is correlate the delete sql in
any way with the exists sql
Note that you have three tables: IPAPDTL, D, H
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn Gundermann
Sent: Friday, December 14, 2012 7:54 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL Delete with JOIN
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.
midrange.com
