Hi,

I'm new to DB2 databasees. Can you please confirm if it is possible to use join the DELETE statement in DB2.

Here the statement I use and get an error:

DELETE MTST.DTC_MTCH_COMMENT A
FROM MTST.DTC_MTCH C
WHERE A.MTCH_CNTL_ID = C.MTCH_CNTL_ID
AND A.MTCH_TRD_ID = C.MTCH_TRD_ID
AND A.MTCH_SRC_CD = C.MTCH_SRC_CD
AND A.MTCH_TS = C.MTCH_TS
AND C.MTCH_TS < '2003-03-12-00.00.00.00000'


Thanks!

No you cannot do that.

Andy

From what I remember, DELETE can be used together with a JOIN.
For example, let us say one wants to DELETE all employees from
table: PROD.EMPLOYEE whose job-description is associated with an
'Computer Progammer' job description. (realistic situation ???).
Now assuming that job-descriptions are stored in the
PROD.JOB table, then I believe this DELETE statement would accomplish this:

DELETE FROM PROD.EMPLOYEE A,
PROD.JOB B
WHERE A.JOB_NUMBER = B.JOB_NUMBER
AND B.JOB_DESC = 'COMPUTER PROGRAMMER'

I believe the above DELETE statement will only affect rows on the
PROD.EMPLOYEE table without affecting the rows on the PROD.JOB
table. I would suggest running a test of the above SQL in some interactive testing environment (QMF, or whatever) to test it beforehand.

The above uses a traditional (inner) join as the example. I have never tried it with a LEFT or RIGHT outer join.

regards
dcshnier

Andy, please explain what you want to do (a short explanation).
Saludos, Aloz.

According to the LUW SQL Reference DELETE does not allow a join. To do what you are describing you will need to do it like this:

DELETE FROM PROD.EMPLOYEE as A WHERE
A.JOB_NUMBER IN (SELECT B.JOB_NUMBER from PROD.JOB as B WHERE B.JOB_DESC = 'COMPUTER PROGRAMMER')

Andy

Hi Andy
You are correct - I just tried it and it failed.
I realize the usual way was to do it through a SUB-SELECT, nevertheless, both myself and my colleagues here recall DB2 allowing it through a JOIN. But I just tried it in QMF and it failed. Maybe at one time, it did allow it but due to the danger of such an allowance, that allowance was later removed.
Which is just as well.
regards
dcshnier