对于Oracle SQL，请尝试使用别名

UPDATE Sales_Lead.dbo.Sales_Import SI 
SET SI.AccountNumber = (SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID);

对于SQL Server 2008 +使用MERGE而不是专有的更新…FROM语法有一些吸引力。

它不仅是标准SQL，因此更易于移植，而且在源端有多个连接行的情况下(因此在更新中可能使用多个不同的值)也会引发错误，而不是最终结果不确定。

MERGE INTO Sales_Import
   USING RetrieveAccountNumber
      ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID
WHEN MATCHED THEN
   UPDATE 
      SET AccountNumber = RetrieveAccountNumber.AccountNumber;

然而，不幸的是，选择使用哪一种可能并不完全取决于首选的风格。SQL Server中MERGE的实现一直受到各种错误的困扰。Aaron Bertrand在这里整理了一份报告的清单。

总结其他答案，关于如何仅在“匹配存在”时使用来自另一个表的数据更新目标表，有4种变体

查询和子查询:

update si
set    si.AccountNumber = (
    select ran.AccountNumber 
    from   RetrieveAccountNumber ran
    where  si.LeadID = ran.LeadID
)
from Sales_Import si
where exists (select * from RetrieveAccountNumber ran where ran.LeadID = si.LeadID)

内连接:

update si
set si.AccountNumber = ran.AccountNumber
from Sales_Import si inner join RetrieveAccountNumber ran on si.LeadID = ran.LeadID

交叉连接:

update si
set si.AccountNumber = ran.AccountNumber
from Sales_Import si, RetrieveAccountNumber ran
where si.LeadID = ran.LeadID

走：

merge into Sales_Import si
using RetrieveAccountNumber ran on si.LeadID = ran.LeadID 
when matched then update set si.accountnumber = ran.accountnumber;

所有的变体都是更少的琐碎和可以理解的，我个人更喜欢“内部连接”选项。但其中任何一种都可以使用，开发者必须根据自己的需求选择“更好的选项”

从性能的角度来看，使用join的变体更可取:

MYSQL(这是我最喜欢的恢复所有特定列reasonId值的方法，基于主键id等价)

UPDATE `site` AS destination  
INNER JOIN `site_copy` AS backupOnTuesday 
      ON backupOnTuesday.`id` = destination.`id`
SET destdestination.`reasonId` = backupOnTuesday.`reasonId`

谢谢你的回复。但我找到了一个解决办法。

UPDATE Sales_Import 
SET    AccountNumber = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  Sales_Import.leadid =RetrieveAccountNumber.LeadID) 
WHERE Sales_Import.leadid = (SELECT  RetrieveAccountNumber.LeadID 
                             FROM   RetrieveAccountNumber 
                             WHERE  Sales_Import.leadid = RetrieveAccountNumber.LeadID)  

对于Oracle SQL，请尝试使用别名

UPDATE Sales_Lead.dbo.Sales_Import SI 
SET SI.AccountNumber = (SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID);