how to delete?

MarkhamDBA · #1 (**permalink**) 08-17-10, 12:37

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

db2girl · #2 (**permalink**) 08-17-10, 14:37

How about this:
delete from t1 where exists (select 1 from T1 , T2 ,T3 where T1.col1=T2.col1 and T1.col2=T3.col1)

db2girl · #3 (**permalink**) 08-17-10, 14:58

I tried with some data and it deleted all records from t1 - not good

MarkhamDBA · #4 (**permalink**) 08-17-10, 15:04

Quote:

Originally Posted by db2girl

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.

tonkuma · #5 (**permalink**) 08-17-10, 15:38

Quote:

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
       )
;

MarkhamDBA · #6 (**permalink**) 08-18-10, 10:01

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech