为SQL Server转换MS Access查询

如果您觉得使用可更新的CTE很方便:

with cte as (
    select
        b39.comptrnm b39_comptrnm
        b39.bssi_loc_id b39_bssi_loc_id,
        tpr.comptrnm tpr_comptrnm,
        tpr.locatnid tpr_locatnid
    from dbo.tpr00100 tpr
    inner join dbo.upr10302 upr on tpr.staffid = upr.staffid
    inner join dbo.b3980280 b39 on tpr.comptrnm = b39.comptrnm
    where b39_comptrnm is null
)
update cte 
set b39_comptrnm = tpr_comptrnm, b39_bssi_loc_id = tpr_locatnid

left join