Home » SQL & PL/SQL » SQL & PL/SQL » DELETE (Oracle 9i)
icon7.gif  DELETE [message #359093] Thu, 13 November 2008 13:08 Go to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Hello to all!

Can somebody tell me how can i create a procedure that eliminates values from one table, if that same value exists in another table.Well i want to make a comparison and if it finds that value in one table eliminates in other....

Tables examples: APP_TMP and APP_TMP_WORK


thanks Smile
Re: DELETE [message #359095 is a reply to message #359093] Thu, 13 November 2008 13:14 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@oliveiraum,

Please read OraFAQ Forum Guide.

After that can you please post what actually you tried with the help of a test case and where exactly you faced the problem in achieving the result.


Regards,
Jo
Re: DELETE [message #359126 is a reply to message #359093] Thu, 13 November 2008 16:33 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Hello!!
IF 
      select * from APP_TMP_FVENDAS_WORK T where T.NIF IN(
	    SELECT NIF FROM APP_TMP_FVENDAS 
	  )
	  THEN
        DELETE * From APP_TMP_FVENDAS_WORK

	END IF;

here is part of the code....

regards
rui

mod: added code tags

[Updated on: Thu, 13 November 2008 16:36] by Moderator

Report message to a moderator

Re: DELETE [message #359157 is a reply to message #359093] Thu, 13 November 2008 23:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Where did you get this funny syntax ?

Try WHERE EXIST in Oracle 9i Or
Try MERGE in Oracle 10g.

Smile
Rajuvan
Re: DELETE [message #359225 is a reply to message #359093] Fri, 14 November 2008 03:45 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
hello!!!


IF
select NIF from APP_TMP_FVENDAS where PROCESSED_FLG = 'Y' and here exists (

SELECT NIF FROM APP_TMP_FVENDAS
)

THEN
DELETE From APP_TMP_FVENDAS_WORK

Do you mean like this?
will it erase the line from the table APP_TMP_FVENDAS_WORK if exists in APP_TMP_FVENDAS?

thanks

Re: DELETE [message #359226 is a reply to message #359093] Fri, 14 November 2008 03:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Where did you get this syntax ? None of the above links tells like this. You could have got lots of example if you put some effort .

See the WHERE EXISTS EXAMPLE and implement as per your requirement.

Smile
Rajuvan.
Re: DELETE [message #359227 is a reply to message #359225] Fri, 14 November 2008 04:00 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
@oliveiram

Search for the syntax of delete with exists in internet.

One example is given here:
http://www.techonthenet.com/sql/exists.php
Re: DELETE [message #359231 is a reply to message #359093] Fri, 14 November 2008 04:47 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
DELETE FROM APP_TMP_FVENDAS_WORK P WHERE EXISTS
(
select NIF from APP_TMP_FVENDAS T where PROCESSED_FLG = 'Y'
and P.NIF = T.NIF
) ;


I hope this works and it's well formatted.

But do i have to make a LOOP so that he cleans all lines from the table?

Rui Smile
Re: DELETE [message #359235 is a reply to message #359093] Fri, 14 November 2008 05:19 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Sorry for the 2 messages, i was having problems with the internet....

Embarassed
Re: DELETE [message #359236 is a reply to message #359235] Fri, 14 November 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I deleted the duplicate when you were posting your last one.

Regards
Michel
Re: DELETE [message #359238 is a reply to message #359231] Fri, 14 November 2008 05:23 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@oliveiraum,

oliveiraum wrote on Fri, 14 November 2008 16:16

But do i have to make a LOOP so that he cleans all lines from the table?

I do hope you meant "to delete all the records based on the condition you supplied". Anyways you don't need any loop for that.

However, if you want to delete all the records(i.e "empty your table") remove the WHERE CLAUSE and the conditions following from your DELETE Statement. i.e

 DELETE FROM <table_name>


Hope this helps.
Regards,
Jo
Re: DELETE [message #359239 is a reply to message #359093] Fri, 14 November 2008 05:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Sorry , I couldn't delete the duplicate post when I checked for it . ( Michel deleted before me )

Smile
Rajuvan
Re: DELETE [message #359240 is a reply to message #359093] Fri, 14 November 2008 05:30 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Thank you all for the help...


will try to amke it hapen...thank you really

Razz
Re: DELETE [message #359241 is a reply to message #359093] Fri, 14 November 2008 05:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

First point to notice ;

LOOP constructs wont Work in SQL 
LOOP Constructs work only in PL/SQL 


Smile
Rajuvan.
Re: DELETE [message #359242 is a reply to message #359093] Fri, 14 November 2008 05:33 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Welcome Really Smile

Rajuvan
Re: DELETE [message #359243 is a reply to message #359239] Fri, 14 November 2008 05:34 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

And here I was trying in vain to post an answer getting an error "The Message Doesn't exists" http://img2.mysmiley.net/imgs/smile/mad/mad0177.gif

Anyways, hope OP got the solution. http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

Regards,
Jo
Previous Topic: Variable in select "IN" clause (merged)
Next Topic: ORA-00600: internal error code
Goto Forum:
  


Current Time: Sat Dec 10 22:51:49 CST 2016

Total time taken to generate the page: 0.10350 seconds