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