Re: Cursor with Update query
Correct, Informix does not support the FROM clause in an update statement.
Try this subquery version:
UPDATE c
SET c_type='E' and c_info=:v_email
WHERE exists (
select 1
from p
join t
on p.acct_id = t.acct_id
and c.cust_id = p.cust_id
);
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Sep 22, 2011 at 6:06 AM, MANSI MALIK <mansi_pisces23@yahoo.com>wrote:
> Thanks for responding to the query.
> I have made the changes told by you but still I am getting the same error.
> The cursor is as follows:
> EXEC SQL
> Declare mm cursor for
> select c_cust_id,t_email FROM t
> INNER JOIN p On p.p_acct_id = t.t_acct_id
> INNER JOIN c
> On c.c_cust_id = p.cust_id> for update;
> string v_email
> INTEGER v_cust_id
> EXEC SQL open mm;
> for(;;)
> {
> EXEC SQL fetch mm into : v_cust_id,:v_email;
> if (strncmp(SQLSTATE, "00", 2) != 0)
> break;
> EXEC SQL update c
> set c_type='E' and c_info=:v_email
> where current of mm;
> if (strncmp(SQLSTATE, "00", 2) != 0)
> break;
> }
>
> Earlier I had written an update query for this purpose,
> UPDATE c
> SET c_type='E' and c_info=:v_email
> FROM t
> INNER JOIN p On p.p_acct_id = t.t_acct_id
> INNER JOIN c
> On c.c_cust_id = p.cust_id
> But it seems that update and from are not a good combination.
> If you know some other way of doing this also please let me know. The
> purpose
> is to update all the rows in table c with email ids in table t corrsponding
> to
> the acct_id as well as cust_id.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf303bfbc4a5300404ad86e43f
But how can we update a row when we are fetching the data from same table.Secondly, I am getting the error in the beginning i.e. EXEC SQL statement and if remove this and try executing then I get error in DECLARE statement.