sql-server - 如何在从自身更新同一个表时为表添加别名？

Question

如何在从自身更新同一个表时为表添加别名？

我的查询:

update accounts set adusername = x.adusername 
from Accounts x
where x.AccountName = accounts.rev and x.ok>10 and accounts.ok in (0,1,2,3) 
and x.ADUserName is not null and accounts.ADUserName is null

错误

Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "accounts.rev" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "accounts.ok" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "accounts.ok" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "accounts.ok" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "accounts.ok" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "accounts.ADUserName" could not be bound.

Answer 1

这个怎么样:

UPDATE a
SET a.adusername = x.adusername 
FROM Accounts x
INNER JOIN Accounts a ON x.AccountName = a.rev
WHERE x.ok>10
    AND a.ok in (0,1,2,3) 
    AND x.ADUserName is not null
    AND a.ADUserName is null