I checked version 9 and this update works

update site set ss.siteCat = 1
from site as ss join trans t
on t.siteno = ss.siteno
where ss.recno = 9000016


>>I now prefer using this syntax:
>>

>>;with cteToUpdate (select goes here)
>>
>>select * from cteToUpdate -- if this works OK, then comment it
>>
>>update cteToUpdate set ...
>>
>>

>>
>>This is SQL Server proprietary code.
>
>I am not familiar with the "with" syntax. Could you elaborate more on it? Basically, I am looking at one SQL. I do not want to execute something in SSMS, for example, from various commands in a row. So, I am interested to know if there is a more conventional syntax we should use in those circumstances.