I take all that back. This does not work.
update ss set ss.siteCat = 1
from site as ss join trans t
on t.siteno = ss.siteno
where ss.recno = 9000016


>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.