Results 1 to 6 of 6

Thread: DB2 delete

  1. #1
    Join Date
    Dec 2002
    Posts
    12

    Unanswered: DB2 delete

    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!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No you cannot do that.

    Andy

  3. #3
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by ARWinner
    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

  4. #4
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    Andy, please explain what you want to do (a short explanation).
    Saludos, Aloz.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

    Originally posted by dcshnier
    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

  6. #6
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by ARWinner
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •