Home » SQL & PL/SQL » SQL & PL/SQL » DML statement
DML statement [message #221053] Fri, 23 February 2007 08:44 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I wish to remove all the concern records from J63 tables matchs similarly with K63 tables on the basis of comumn column c1 and ticketid.

DELETE FROM J63 a WHERE ticketid IN ( SELECT c1 FROM K63 b
WHERE a.ticketid=b.c1 and integerdate ( c3 ) < add_months( sysdate, -36 ))

integerdate is a function name.

Can you suggest me whether it is correct or not?

Is there any better way to remove records quickly like join options?


Re: DML statement [message #221100 is a reply to message #221053] Fri, 23 February 2007 20:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You dont need to correlate the sub-query with IN.

DELETE FROM J63 a 
WHERE ticketid IN ( 
  SELECT c1   
  FROM K63 b
  WHERE integerdate ( c3 ) < add_months( sysdate, -36 )
)


Make sure the statistics are up-to-date on both tables.

This is the best way to do it. Oracle should convert it into a join internally.

Ross Leishman
Re: DML statement [message #221317 is a reply to message #221100] Mon, 26 February 2007 04:29 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Thanks for the posted nmodified query.

Can you please expalin me about the sent comment that statistics are up-to-date on both tables?


As I know that the if there is similar records on the basis of common column then it should be removed from the H tables.

Moreover, there would be similar number of common records in both tables and I wish to keep the rest of records intact in tables.

Please send your comments or suggestions as ealry as possble so that I can procced.

Re: DML statement [message #222653 is a reply to message #221053] Mon, 05 March 2007 15:54 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
what he means is that if the optimizer statistics are up to date, the delete will run faster, but if they are not, the delete will still work.
Re: DML statement [message #223043 is a reply to message #222653] Wed, 07 March 2007 05:47 Go to previous message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Thanks for your valuable input.
Previous Topic: Problem in Creating a View
Next Topic: urgent pls help me in this query
Goto Forum:
  


Current Time: Tue Dec 06 06:33:35 CST 2016

Total time taken to generate the page: 0.07101 seconds