Informix SQL

jeanvaljean

I have 3 tables t1, t2, and t3.
Each has a primary key of 'job_no'.

t3 has a field called 'status'.

I wish to update t3.status by setting it to “XXX” when certain conditions are met.

My syntax looks like this :-

select t1.job_no
from t1, t2, t3
where t1.job_no = t2.job_no
and t2.job_no = t3.job_no
and (more conditions)
into temp ttemp;
update t3
set t3.status = “XXX”
where ttemp.job_no = t3.job_no

but I get error :- 522: Table (ttemp) not selected in query

Can anyone advise how to get around this ?

Thanks

pragtastic

To start, I have no familiarity with Informix SQL. But, from reading the syntax of that statement, the line "into temp ttemp;" appears to be where you initially create this ttemp table that is referenced in the error. The next line "update t3" looks to be a new statement, which I'm guessing might no longer have access to the ttemp table created in the previous statement.

All just a guess, but might be something to look into. Maybe to help confirm this suspicion you could try running the first statement by itself and see if an error is reported.

Butters

Why do something in 3 statements when you can do it in 1. Unless you are filtering out data from the temp table, which isn't necessary in the first place.

update t3
set t3.status = “XXX”
where t1.job_no = t2.job_no
and t2.job_no = t3.job_no
and (more conditions)