File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes delete & select subquery.. giving me tough time.. plz hlep Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "delete & select subquery.. giving me tough time.. plz hlep" Watch "delete & select subquery.. giving me tough time.. plz hlep" New topic
Author

delete & select subquery.. giving me tough time.. plz hlep

Jack Daniel
Ranch Hand

Joined: Jun 15, 2002
Posts: 163
Hi everybody,

well, firstly i am working on DB2 on iseries,.. i have a table(T1), which has many columns, but i am specific of 3 columns,.. and another table(T2) which has the same 3 columns.

Now what i want to do is, delete all the rows in T1, if the corresponding values of three columns are PRESENT in T2 (which involves a delete and select sub-query), but i am just not able to get it working,.. throwing some problem.. Is it because of some lack of support in DB2??

These are the ways I tried:

(1)
DELETE FROM LIBRARY.T1 WHERE (LIBRARY.T1.FNBR110=LIBRARY.T2.FNBR110 AND LIBRARY.T1.REFID171=LIBRARY.T2.REFID171 AND LIBRARY.T1.CNTNBR171=LIBRARY.T2.CNTNBR171)

(2)
DELETE FROM LIBRARY.T1 WHERE FNBR110,REFID171,CNTNBR171 IN (SELECT FNBR110,REFID171,CNTNBR171 FROM LIBRARY.T2)

(3)
DELETE FROM LIBRARY.T1 (SELECT FNBR110,REFID171,CNTNBR171 FROM LIBRARY.T2)

Any help to fix this, is greatly appreciated.. i'll keep trying to figure out how to run this..

Anyway thanks again..
Jack Daniel
Ranch Hand

Joined: Jun 15, 2002
Posts: 163
one more thing to mention: NOT all the columns are the same in both the tables T1 & T2
thx
Jack Daniel
Ranch Hand

Joined: Jun 15, 2002
Posts: 163
well, since the number of records are not huge(in thousands or millions), i have done that by running two prepared statements, where the first one is storing the 3 columns data from T2 and the 2nd one is deleting from T1 based on the values stored (by the 1st statement).

Ok work is done,.. but still i would like to know, if using just one SQL statement, how is this achieved?? because i know saving the three columns from T2 (i am doing it in ArrayLists..) and then deleting EACH row based on what data i collected, is not good on performance point of view..

Thanks..
want to hear from SQL.. guys..
Jack Daniel
Ranch Hand

Joined: Jun 15, 2002
Posts: 163
GOTCHA !!!

DELETE FROM Lib.T1 WHERE EXISTS (SELECT * FROM Lib.T2 WHERE (T1.FNBR110=T2.FNBR110 AND T1.REFID171=T2.REFID171 AND T1.CNTNBR171=T2.CNTNBR171

great week-end ;-)
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

Prashant,
Thanks for posting the final query.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: delete & select subquery.. giving me tough time.. plz hlep