Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jack Daniel
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
one more thing to mention: NOT all the columns are the same in both the tables T1 & T2
thx
 
Jack Daniel
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34179
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Prashant,
Thanks for posting the final query.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic