Error: Operation must use an updatable query 1

[nithink]

Error: Operation must use an updatable query

While running the below query, I'm getting the error, Operation must use an updatable query.

UPDATE CapgComp_init AS A SET A.cl_id = (SELECT LEFT(B.cl_id, 4)&T.future_state_account_num
FROM CapgComp_init B
INNER JOIN current_future_acct_num AS T ON MID(B.cl_id,4,len(cl_id)) =T.current_pim_account_num
WHERE MID(A.cl_id, 4, len(A.cl_id)) = T.current_pim_account_num);


Can anyone pls let me know... Thanks...

 

[flyover789]

why are you using the table CapgComp_init twice?

if I understand your SQL well, you could also use (not tested):

UPDATE CapgComp_init AS A 
INNER JOIN current_future_acct_num AS T 
ON MID(A.cl_id,4,len(cl_id)) =T.current_pim_account_num
SET A.cl_id = LEFT(A.cl_id, 4)&T.future_state_account_num;

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[nithink]

Thanks Martin very much... it worked fine...

I've one more question... I've a query which creates an output with many columns. I want to transfer the whole query output to a new table.. The query output has some decimal values too which I want to have it the same...It should not get rounded... I just want to have the same values in the table as it is in the query output...

Thanks much....

 

[flyover789]

Hi nithink,

thanks for the star ;o)

I'd suggest that you start a new thread with your second question.

I'll be pleased to help again.

Greetings,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[nithink]

Have started a new thread... sorry abt that...