Home » SQL & PL/SQL » SQL & PL/SQL » Update record based on previous record (Oracle 11g)
Update record based on previous record [message #636775] Sat, 02 May 2015 07:38 Go to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi Guys,

I having two tables where in i will be inserting the records based on input parameter passed in my stored procedure and i would like to update those successful records which were inserted into another table with flag notification

for eg

create or replace procedure (a in number, b in number , c out varchar2) is

begin
insert into Table
select col1,col2 from Table_2 where col = a, col2 = b ;

count:=SQL%ROWCOUNT;
if count>0
then
update Main_table set flag = 'S' where col = a, col2 = b ;
else
c:='failed';
end if
end;
/

is there any alternative way to update in main_table with success flag as 'S' based on those records only succseefully inserted in Table
Re: Update record based on previous record [message #636776 is a reply to message #636775] Sat, 02 May 2015 07:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your code is full of bugs. You haven't even given the procedure a name. You are using a reserved word as an identifier. Your predicates are syntactically wrong.

Have you actually tried to comple it?
Re: Update record based on previous record [message #636777 is a reply to message #636776] Sat, 02 May 2015 07:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>is there any alternative way to update in main_table with success flag as 'S' based on those records only succseefully inserted in Table
TRIGGER is alternative, but I think this whole scenario is silly & nonsensical.

What is value of "c" for a successful INSERT.

If 1 or more rows succeed yet 1 or more rows fail to INSERT, is this operation a success or failure?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Re: Update record based on previous record [message #636778 is a reply to message #636777] Sat, 02 May 2015 07:54 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
HI,

i don't want the trigger to be written only i wanted to know is if the records are inserted successfully i need to update those records in main table with flag as 'S'
Re: Update record based on previous record [message #636779 is a reply to message #636778] Sat, 02 May 2015 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rohit_shinez wrote on Sat, 02 May 2015 05:54
HI,

i don't want the trigger to be written only i wanted to know is if the records are inserted successfully i need to update those records in main table with flag as 'S'


How do you differentiate between records that were recently INSERTed & those that have been in TABLE previously?

Explain how this "FLAG" will actually be used within application logic.
Re: Update record based on previous record [message #636780 is a reply to message #636779] Sat, 02 May 2015 08:04 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
actually this procedure will be called through java, so whenever its been called i will insert a record in table and will update the status on another table which will also hold the same records.

Re: Update record based on previous record [message #636782 is a reply to message #636780] Sat, 02 May 2015 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rohit_shinez wrote on Sat, 02 May 2015 06:04
actually this procedure will be called through java, so whenever its been called i will insert a record in table and will update the status on another table which will also hold the same records.




if UPDATE does what you desire, why do you want, need, desire any alternative?

Since INSERT adds new rows & values to table, how can you be sure that a record exists MAIN_TABLE to be UPDATED?

Why do you not directly answer my questions?
Re: Update record based on previous record [message #636877 is a reply to message #636782] Mon, 04 May 2015 13:18 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
if the row is in the table then the insert worked. You will never have a row in the table where the insert failed. Why do you even need the flag?
Previous Topic: exception for field width
Next Topic: list of columns of a table
Goto Forum:
  


Current Time: Thu Mar 28 04:04:16 CDT 2024