Results 1 to 6 of 6

Thread: how to delete?

  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: how to delete?

    My sql is rusty I have a statement which selects a number of records from 3 tables:

    select T1.col1,T1.col2,T2.col1,T3.col1
    from table1 T1 ,table2 T2 ,table3 T3
    where T1.col1=T2.col1
    and T1.col2=T3.col1

    How do I modify this statement to delete those records from table1? table1 does have PKs.
    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    How about this:
    delete from t1 where exists (select 1 from T1 , T2 ,T3 where T1.col1=T2.col1 and T1.col2=T3.col1)

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I tried with some data and it deleted all records from t1 - not good

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by db2girl View Post
    I tried with some data and it deleted all records from t1 - not good
    yeah, i tried it too and deleted all records even if I had fetch first 3 rows only in the inner query.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    delete from t1 where exists (select 1 from T1 , T2 ,T3 where T1.col1=T2.col1 and T1.col2=T3.col1)
    Remove T1 from exists subselect, like:
    Code:
    DELETE FROM table1 T1
     WHERE EXISTS
           (SELECT 0
              FROM table2 T2 , table3 T3
             WHERE T1.col1 = T2.col1
               AND T1.col2 = T3.col1
           )
    ;

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    perfect. Thank you very much, Tonkuma.

    i could not find any useful (for my case) examples with EXISTS in any book (db2 documentation, db2 certification books) for some reason.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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