Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: does this call for a cursor? how?

    Hi, sorry I am kind of stuck how to do this correctly.
    The following is my problem statement:

    There are three tables: TAB1 TAB2 TAB3

    There is a 1 to many relationship between TAB2 and TAB1.

    For every row in TAB2 that has a match in TAB1, they are related via
    TAB2.col3 = TAB1.col2

    I want to delete all those rows in both tables that match, but I have to
    delete those rows in TAB1 first because of dependency constraints.
    The primary key for TAB2.col1

    Therefore I need to have a select block first and store it somewhere(?)

    select
    TAB2.col1
    from
    TAB2
    where
    TAB2.col3 IN
    (select
    TAB1.col2
    from
    TAB1
    where
    TAB1.col1 IN
    (select
    TAB3.col1
    from
    TAB3))



    Then I delete all those rows in TAB1 that has a match in TAB3

    delete from TAB1
    where
    TAB1.col1 IN
    (select
    TAB3.col1
    from
    TAB3))

    now i delete those rows in TAB2 from the result set of the first sql block.

    thanks for any help.

  2. #2
    Join Date
    Jul 2006
    Posts
    111
    Hi, sorry I am kind of stuck how to do this correctly.
    The following is my problem statement:

    There are three tables: TAB1 TAB2 TAB3

    There is a 1 to many relationship between TAB2 and TAB1.

    For every row in TAB2 that has a match in TAB1, they are related via
    TAB2.col3 = TAB1.col2

    I want to delete all those rows in both tables that match, but I have to
    delete those rows in TAB1 first because of dependency constraints.
    The primary key for TAB2.col1

    Therefore I need to have a select block first and store it somewhere(?)

    Code:
    select 
         TAB2.col1
    from
         TAB2
    where
         TAB2.col3 IN
         (select 
               TAB1.col2
          from 
               TAB1
          where
              TAB1.col1 IN
              (select 
                    TAB3.col1
               from
                    TAB3))

    Then I delete all those rows in TAB1 that has a match in TAB3

    Code:
    delete from TAB1
          where
              TAB1.col1 IN
              (select 
                    TAB3.col1
               from
                    TAB3))
    now i delete those rows in TAB2 from the result set of the first sql block.

    thanks for any help.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wonder if changing the RI / Relationship to "Delete Cascade" would help?

    there are times and places where delete cascade isnt that smart, but it may well be appropriate here.

    btw I think you ought to rethink your table design... calling them tab1,2,3 & Col1,2,3 is just being masochistic (unless you are trying to hide the nature of the table here which is fine...)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2006
    Posts
    111

    does this call for cursor? how?

    Hi, sorry I am kind of stuck how to do this correctly.
    The following is my problem statement:

    There are three tables: TAB1 TAB2 TAB3

    There is a 1 to many relationship between TAB2 and TAB1.

    For every row in TAB2 that has a match in TAB1, they are related via
    TAB2.col3 = TAB1.col2

    I want to delete all those rows in both tables that match, but I have to
    delete those rows in TAB1 first because of dependency constraints.
    The primary key for TAB2.col1

    Therefore I need to have a select block first and store it somewhere(?)

    Code:
    select 
         TAB2.col1
    from
         TAB2
    where
         TAB2.col3 IN
         (select 
               TAB1.col2
          from 
               TAB1
          where
              TAB1.col1 IN
              (select 
                    TAB3.col1
               from
                    TAB3))

    Then I delete all those rows in TAB1 that has a match in TAB3

    Code:
    delete from TAB1
          where
              TAB1.col1 IN
              (select 
                    TAB3.col1
               from
                    TAB3))
    now i delete those rows in TAB2 from the result set of the first sql block.

    thanks for any help.

  5. #5
    Join Date
    Jul 2006
    Posts
    111
    hi healdem,

    thanks for the reply, and yes I am trying to hide the table names... btw, I have transferred this query to the sql forum.

    thanks again... g11DB

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If not really sure ...but

    DELETE FROM Table3 WHERE Exists (SELECT * FROM Table2 Correlate)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dupe thread from Access forum merged into this one

    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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