How to Update a table from output of stored procedure

Database
Enthusiast

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

4 REPLIES
Enthusiast

Re: How to Update a table from output of stored procedure

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;

Supporter

Re: How to Update a table from output of stored procedure

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

Enthusiast

Re: How to Update a table from output of stored procedure

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

Supporter

Re: How to Update a table from output of stored procedure

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.