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

You should do something like this:

Update using an Inner Join - Microsoft SQL Server answers

Thanks John
Unfortunately, my requirement has changed since my first post.
Rather than setting t3 status to 'XXX', I now need to set it to the
value of status now being held in the t1 table.
I don't think the examples in "Update using an Inner Join"
will do for my new scenario.
Any further advise would be most grateful !

Using your original methodology you could:

select t1.job_no, t1.status
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 = (select ttemp.status
from ttemp
where ttemp.job_no = t3.job_no)
where t3.jobno in (select ttemp.job_no from ttemp);

Hi mjl
Many thanks : your code works great.

Can I ask another question ?
If table t3 has more than one field to be updated from t1,
then I can enhance the 1st part as follows :
But how would the update command look ?

doesn't seem to work.

many thanks again.

You can have only one "set" in an update statement & the various values are separated with commas. Using your method this MAY work
(untested but inefficient) as long as the "where" condition of the subquery(s) returns only 1 row:

update t3
set t3.status = (select ttemp.status from ttemp
where ttemp.job_no = t3.job_no),
t3.f2 = (select ttemp.f2 from ttemp
where ttemp.job_no = t3.job_no),
t3.f3 = (select ttemp.f3 from ttemp
where ttemp.job_no = t3.job_no)
where t3.jobno in (select ttemp.job_no from ttemp);

================================================

this will work, same "subquery only returns 1 row" rule applies

update t3
set (t3.status, t3.f2, t3.f3) = (select ttemp.status, ttemp.f2, ttemp.f3
from ttemp
where ttemp.jobno = t3.jobno)
where t3.jobno in (select ttemp.job_no from ttemp);