我对foo也有同样的问题。对于在bar中没有匹配键的foo行，New被设置为null。我在Oracle做了类似的事情:

update foo
set    foo.new = (select bar.new
                  from bar 
                  where foo.key = bar.key)
where exists (select 1
              from bar
              where foo.key = bar.key)

对于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在这里整理了一份报告的清单。

如果以上答案不适合你，试试这个

Update Sales_Import A left join RetrieveAccountNumber B on A.LeadID = B.LeadID
Set A.AccountNumber = B.AccountNumber
where A.LeadID = B.LeadID 

这将允许您根据未在另一个表中找到的列值更新表。

UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN (
        SELECT * 
        FROM (
                SELECT table1.id
                FROM  table1 
                LEFT JOIN table2 ON ( table2.column = table1.column ) 
                WHERE table1.column = 'some_expected_val'
                AND table12.column IS NULL
        ) AS Xalias
)

这将根据在两个表中找到的列值更新一个表。

UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN (
        SELECT * 
        FROM (
                SELECT table1.id
                FROM  table1 
                JOIN table2 ON ( table2.column = table1.column ) 
                WHERE table1.column = 'some_expected_val'
        ) AS Xalias
)

我认为这是一个简单的例子，可能有人会更容易理解，

        DECLARE @TB1 TABLE
        (
            No Int
            ,Name NVarchar(50)
        )

        DECLARE @TB2 TABLE
        (
            No Int
            ,Name NVarchar(50)
        )

        INSERT INTO @TB1 VALUES(1,'asdf');
        INSERT INTO @TB1 VALUES(2,'awerq');


        INSERT INTO @TB2 VALUES(1,';oiup');
        INSERT INTO @TB2 VALUES(2,'lkjhj');

        SELECT * FROM @TB1

        UPDATE @TB1 SET Name =S.Name
        FROM @TB1 T
        INNER JOIN @TB2 S
                ON S.No = T.No

        SELECT * FROM @TB1

MS Sql

UPDATE  c4 SET Price=cp.Price*p.FactorRate FROM TableNamea_A c4
inner join TableNamea_B p on c4.Calcid=p.calcid 
inner join TableNamea_A cp on c4.Calcid=cp.calcid 
WHERE c4..Name='MyName';

Oracle 11 g

        MERGE INTO  TableNamea_A u 
        using
        (
                SELECT c4.TableName_A_ID,(cp.Price*p.FactorRate) as CalcTot 
                FROM TableNamea_A c4
                inner join TableNamea_B p on c4.Calcid=p.calcid 
                inner join TableNamea_A cp on c4.Calcid=cp.calcid 
                WHERE p.Name='MyName' 
        )  rt
        on (u.TableNamea_A_ID=rt.TableNamea_B_ID)
        WHEN MATCHED THEN
        Update set Price=CalcTot  ;