All Forums Database
terankit 77 posts Joined 03/12
04 Jul 2012
How to Update a table from output of stored procedure

Hi All,

 

I have to update a coulmn (CNTL_ID) in Table A using the output of Stored Proc.

CALL GETNEXTLOADID(NEXTID) gives us the next available ID.

I tried:

UPDATE TABLEA   
FROM TABLEA,    
(CALL GETNEXTLOADID(NEXTID)) T(NID)
SET CNTL_ID = T.NID                      
WHERE condition;          

But it is not working.

Please correct this query or any other way to do this.

Thanks,

Terankit

terankit 77 posts Joined 03/12
04 Jul 2012

the definition of Stored Proc is:

 

REPLACE PROCEDURE GETNEXTLOADID(OUT NEXTID INTEGER)

BEGIN

LOCKING TABLE tbl1 FOR WRITE

UPDATE tbl1

SET CNTRL_ID = CNTRL_ID + 1;

SELECT CNTRL_ID INTO :NEXTID FROM tbl1;

END;

ulrich 816 posts Joined 09/09
04 Jul 2012

so why don't you just use tbl1 in the update statement?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
05 Jul 2012

Hi Ulrich,

 

Can you please explain? The only issue is to use the output of Store Proc to update a column in another table.

 

Thanks,

Terankit

ulrich 816 posts Joined 09/09
05 Jul 2012

Its not going to work.

In your example it would not be needed as you could also

UPDATE a  
FROM TABLEA a,    
         tbl1 t
SET CNTL_ID = T.CNTRL_ID                      
WHERE condition;          

 

If tbl1 can be updated from different sessions at the same time then do the update and insert select the value in a volatile or global temp table within the SP.

Use the volatile or global temp table in your update in this case.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.