Home » SQL & PL/SQL » SQL & PL/SQL » urgent help needed...
urgent help needed... [message #289911] Wed, 26 December 2007 09:23 Go to next message
Messages: 1
Registered: December 2007
Junior Member
The problem is... table A and table B are related with B as parent table and A child. The value of id (primary key) of table B needs to be populated with the column Bid when the following where condition satisfies.
A.x=B.x and A.y=B.y and A.z=B.z for every value of A
My problem is to create either a procedure, a function or a package which populates the value of B.id in table A with the above where condition for id value of A
I created a function which gives me the return value of Bid but unable to update the table as several ids of table A have the same value of Bid.

select A.id into bid from A,B where A.x=B.x and A.y=B.y and A.z=B.z;
return bid;

but in the same function I am unable to update

update A set bid=bid;

taking the return value from the function.
I tried the update statement in two ways

1)by putting just the update statement in the function. it gave an error which says

4/1 PL/SQL: SQL Statement ignored
5/23 PL/SQL: ORA-00925: missing INTO keyword

2) I tried my another function where i retrieve the value of

WHERE a.x= p_ID
AND a.y = code

AND upper(TRIM(a.z)) = upper(p_S_NAME)
AND (to_number(nvl(P_H_N,'0'))
BETWEEN to_number(a.h) AND
AND substr(P_q,1,5) = substr(a.q,1,5)
AND upper(P_r) = upper(a.r) and b.id=bid
update a set adrlib_id=(select b.id from b where a.x=b.x and a.y=b.y and a.z = b.z and (a.r between b.r and b.r1));

in the second instance, the retrieval of bid works fine but the update is ignored. please suggest. I also want to mention that for every value of a.id there are more than one values of bid. so the update will return more than one rows. I am unable to understand why? I am pretty weak in pl/sql kindly help me if am making some mistake. I NEED HELP FAST...THANKS IN ADVANCE
Can anyone suggest me the solution?

kindly help.

Re: urgent help needed... [message #289913 is a reply to message #289911] Wed, 26 December 2007 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I suggest you read and follow OraFAQ Forum Guide.
You will find "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
You will find you have to post your Oracle version.
You will find you have to post the actual code.
You will find you must NOT post words like urgent.
You will find you have to post a meaningfull title.

Re: urgent help needed... [message #289957 is a reply to message #289911] Wed, 26 December 2007 22:32 Go to previous message
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Nandan,

You could do this task using a single update statement instead.
Please search for various updates syntax.

1. Update
2. Update ..... where
3. Update ..... where ... EXISTS

Thumbs Up
Previous Topic: Multiple Function definitions and Null datatype
Next Topic: ORA-01578: ORACLE data block corrupted
Goto Forum:

Current Time: Tue Oct 25 10:42:15 CDT 2016

Total time taken to generate the page: 0.15054 seconds