Re: SQL Delete with JOIN

On 14 Dec 2012 08:54, Glenn Gundermann wrote:

<<SNIP>>

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

I believe that is an extension offered by some SQL providers; i.e. that syntax is beyond the ANS/SQL as implemented by the DB2. There may or may not be newer ANSI SQL defining that or similar syntax. I have no access to the standards documents.

2. <<SNIP>>

create view IPAP_Dtl_Hdr_ADLP as
( select D.*
, ( select H.ADLP
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$
) as ADLP
FROM IPAPDTL D
)
; -- create VIEW with ALWUPD=*YES ALWDLT=*YES
-- only column ADLP would be read-only: see SQL0151

delete
from IPAP_Dtl_Hdr_ADLP
where ADLP = 121213
; -- delete from effective-join query; verified success on v5r3

My recollection of the relational "rules" is that a RDBMS should /understand/ that a JOIN [VIEW] is "updateable". Creating that VIEW using JOIN syntax instead, does not Allow Delete\Update... on v5r3. Perhaps the DB2 for i Database SQL might allow the DELETE statement without having to first CREATE TRIGGER ... INSTEAD OF DELETE.