Trying to delete from a table where there are matching records in other tables.
I've tried different variations of this, but this one returns:
SQL Error [42601]: [SQL0199] Keyword INNER not expected. Valid tokens: USE SKIP WAIT WITH FETCH LIMIT ORDER WHERE OFFSET.
It's basically a cross library / cross database, but can't get DB2 to play along. The Select works just fine, if I replace the delete with SELECT *
DELETE a
FROM INHOUSE.ANDREWCAT a
INNER JOIN ERPLIB.SRBPRG b ON
a.PSPRDC = b.PGPRDC
INNER JOIN ERPLIB.SRBRSD c
ON
b.PGIRGP = c.RDSRTY
AND c.RDTOFI = a.EPNUM AND c.RDSRTY = c.RDWHAT
AND a.EPNUM = 'REM104'
DB2 does not support the syntax you ware using.
Instead:
DELETE INHOUSE.ANDREWCAT a
WHERE EXISTS (SELECT 1
FROM ERPLIB.SRBPRG b JOIN
ERPLIB.SRBRSD c
ON b.PGIRGP = c.RDSRTY
WHERE a.PSPRDC = b.PGPRDC AND
c.RDTOFI = a.EPNUM AND
c.RDSRTY = c.RDWHAT AND
a.EPNUM = 'REM104'
);