Cursor with Update query
I have written a cursor but I am getting a 201:syntax error.My requirements
are as follows:
I have to three tables:p,c and t.
Table t has two attributes(t_acct_id and t_email)
We have to update table c depneding upon data from table t.
In table c, two attributes will be updated(c_type and c_info)
We use another table p for acting as a bridge between the two.
We need to update all the rows in table c which have c_cust_id which have the
corrsponding acct_id present in table t.
For this, we use table p which has p_cust_id as well as p_acct_id.
Table t(acct_id)-->Table p(acct_id)--(cust_id)-->Table (cust_id)-->and finally
update the table c.
Following is the cursor:
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 c_cust_id =@c_cust_id
if (strncmp(SQLSTATE, "00", 2) != 0)
break;
}
EXEC SQL close mm;
Please reply as I am struggling with this issue from quite a long.Thanks in
advance.
Introduce the variables in the update with colon (:) not 'at' (@). Also IB
that you mean to compare c_cust_id to :v_cust_id not c_cust_id. Finally, I
don't see the terminating semi-colon on the update statement. So:
EXEC SQL update c
set c_type='E' and c_info=:v_email
where c_cust_id =:v_cust_id;
Also, instead of doing the comparison in the WHERE clause, since you have an
update cursor, use that to locate the row to be updated, it's significantly
faster:
EXEC SQL update c
set c_type='E' and c_info = :v_email
where CURRENT OF mm;
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 Wed, Sep 21, 2011 at 6:38 AM, MANSI MALIK <mansi_pisces23@yahoo.com>wrote:
> I have written a cursor but I am getting a 201:syntax error.My requirements
> are as follows:
>
> I have to three tables:p,c and t.
> Table t has two attributes(t_acct_id and t_email)
> We have to update table c depneding upon data from table t.
> In table c, two attributes will be updated(c_type and c_info)
> We use another table p for acting as a bridge between the two.
> We need to update all the rows in table c which have c_cust_id which have
> the
> corrsponding acct_id present in table t.
> For this, we use table p which has p_cust_id as well as p_acct_id.
> Table t(acct_id)-->Table p(acct_id)--(cust_id)-->Table (cust_id)-->and
> finally
> update the table c.
>
> Following is the cursor:
> 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 c_cust_id =@c_cust_id
>
> if (strncmp(SQLSTATE, "00", 2) != 0)
>
> break;
>
> }
>
> EXEC SQL close mm;
>
> Please reply as I am struggling with this issue from quite a long.Thanks in
> advance.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd3a1d23a3af004ad728466
On Wed, Sep 21, 2011 at 03:38, MANSI MALIK <mansi_pisces23@yahoo.com> wrote:
> I have written a cursor but I am getting a 201:syntax error.My requirements
> are as follows:
>
> I have to three tables:p,c and t.
> Table t has two attributes(t_acct_id and t_email)
> We have to update table c depending upon data from table t.
> In table c, two attributes will be updated(c_type and c_info)
> We use another table p for acting as a bridge between the two.
> We need to update all the rows in table c which have c_cust_id which have
> the
> corresponding acct_id present in table t.
> For this, we use table p which has p_cust_id as well as p_acct_id.
> Table t(acct_id)-->Table p(acct_id)--(cust_id)-->Table (cust_id)-->and
> finally
> update the table c.
>
> Following is the cursor:
> 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;
>
I don't think you can have a FOR UPDATE cursor on a join query.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--000e0cd3421c66bce504ad7e79a9
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_idfor 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.