Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    7

    Unanswered: Delete Statement with Inner Join

    Hi,

    I am trying to write a delete statement that will read from one table and if there is a match it will delete from a different table and that works fine but I need to do a join or something similar because the 2 tables do not have a column that is similar between them but I have a third table that has columns from both the other tables. The following code does not work because a3.lspart != a4.unfmt.

    DELETE FROM KELLYP.LOSTSALE A3
    WHERE EXISTS (SELECT 'unfmt' from kellyp.newpn a4
    where a3.LSpart = a4.unfmt
    and a3.lslncd = a4.lc
    and a3.lssbrn = branch);

    To help clarify the issue the table LOSTSALE and table NEWPN do not have a similar column that will work to determine what to delete but there is a third table called INSMFT that has both LSPART and UNFMT in it, so I want to match column UNFMT with table INSMFT and use that join to delete from table LOSTSALE with column lspart.

    Sorry in advance I am sure this is confusing to read I appreciate the help.

    Kelly

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I have always found it useful when writing a DELETE to first write it as a "SELECT *" to make sure that only the rows I want to delete are selected. Then I just change "SELECT *" to "DELETE".

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    You can put the join into the exists clause:
    something like this:
    Code:
    DELETE FROM KELLYP.LOSTSALE A3
    WHERE EXISTS (SELECT 'unfmt' from kellyp.newpn a4, INSMFT a5
    where a3.LSpart = a5.LSpart 
    and a4.unfmt= a5.unfmt
    and a3.lslncd = a4.lc
    and a3.lssbrn = branch);

Posting Permissions

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