Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8

    Unanswered: Delete with multiple Joins

    Hello -

    I'm new to this website and also somewhat new to utilizing SQL DB2. I am stuck in a situation where i need to use SQL to delete selected rows out of one table by meeting criteria from other tables. This is what i have written so far:

    delete from
    jhcterry1.atpantrn
    where
    exists

    (select
    b.athash
    from
    jhcterry1.atcardupd a join jhcterry1.atcard b
    on a.athash = b.athash join atmhsts518.atpantrn c
    on
    b.athash = c.atxhash
    where
    a.atcdst <> 'O' and b.atdtis = 093011)

    This query is supposed to delete ONLY records from atpantrn if a.atcdst <> 'O' and b.atdtis = 093011, but for some reason when i run it, it is deleting every record inside the file. What am I doing wrong?? Please help if you can.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There is no relation between tables in where clause and the target table of delete(i.e. jhcterry1.atpantrn).
    So, all rows in jhcterry1.atpantrn would be deleted if exists a row satisfying where condition,
    or no row would be deleted if not exists a row satisfying where condition.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't see the name(or alias) of the target table(i.e. jhcterry1.atpantrn) in exists subquery.

    target table:
    jhcterry1.atpantrn

    tables in exists subquery:
    jhcterry1.atcardupd --- same schema name, different table name.
    jhcterry1.atcard --- same schema name, different table name.
    atmhsts518.atpantrn --- same table name, different schema name.
    Last edited by tonkuma; 10-05-11 at 16:58. Reason: Add concrete example.

  4. #4
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8
    but it is deleting rows out of jhcterry1.atpantrn even when the where condition isn't true. The entire file is being deleted. So jhcterry1.atpantrn needs to be in the sub select? how so?? and where?? Thanks

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    but it is deleting rows ... even when the where condition isn't true.
    I want to repeat this
    There is no relation between tables in where clause and the target table of delete(i.e. jhcterry1.atpantrn).
    So, all rows in jhcterry1.atpantrn would be deleted if exists a row satisfying where condition,
    or no row would be deleted if not exists a row satisfying where condition.
    where a row has no relation with the target table.
    It may or may not exist independent from the status of target.

    Add a correlation name to the target table, like...
    delete from
    jhcterry1.atpantrn AS a
    where
    ...

    then reference the correlation name(i.e. a) in where clause.
    Last edited by tonkuma; 10-05-11 at 17:19.

  6. #6
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8
    Ok i'm with you now on understanding that if even one condition of the where clause is met the entire table is whipped out, but i'm stil not sure "syntax" wise how to incorporate the correlation name. Should I say from: a then join to atcardupd, atcard, and atmhsts518.atpantrn??

    delete from
    jhcterry1.atpantrn as a
    where
    exists
    (select

    from
    jhcterry1.atcardupd a join jhcterry1.atcard b
    on a.athash = b.athash join atmhsts518.atpantrn c
    on
    b.athash = c.atxhash
    where
    a.atcdst <> 'O' and b.atdtis = 093011)

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You are using a correlation name "a" for jhcterry1.atcardupd in where clause.
    So, use another correlation name for the target table, like...
    delete from
    jhcterry1.atpantrn as t
    where
    ...

    then add conditions for t.<column name1>(and t.<column name2> and so on, if neccesary) in where clause.

  8. #8
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8
    lets say i use z for my correlation table. I still don't understand how i incorporate that into the where...what am i comparision "z" to in the where clause? what am i wanting to compare in the other tables against the delete table? z.(column name1 = ??) exists?

    delete from
    jhcterry1.atpantrn as z
    where
    exists
    (select

    from
    jhcterry1.atcardupd a join jhcterry1.atcard b
    on a.athash = b.athash join atmhsts518.atpantrn c
    on
    b.athash = c.atxhash
    where
    a.atcdst <> 'O' and b.atdtis = 093011)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    <column name1>, <column name2>, so on are column names of table jhcterry1.atpantrn.

    If you still don't understand, ask to(or wait answer from) another people.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by cterry View Post
    what am i comparision "z" to in the where clause? what am i wanting to compare in the other tables against the delete table? z.(column name1 = ??) exists?
    This will need to identify the rows that you wish to delete, obviously, since you don't want to delete all of them.

  11. #11
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8
    Do you have any examples of something similar i could reference? I'm still not clear as to how to reference the correspondence table in the where clause. Thanks

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Explain in a human language (preferably English) how you would identify the rows to be deleted. It's easy from there.

Posting Permissions

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