PostgreSQL的:

UPDATE Sales_Import SI
SET AccountNumber = RAN.AccountNumber
FROM RetrieveAccountNumber RAN
WHERE RAN.LeadID = SI.LeadID; 

为未来的开发人员提供的一般答案。

SQL Server

UPDATE 
     t1
SET 
     t1.column = t2.column
FROM 
     Table1 t1 
     INNER JOIN Table2 t2 
     ON t1.id = t2.id;

Oracle(和SQL Server)

UPDATE 
     t1
SET 
     t1.colmun = t2.column 
FROM 
     Table1 t1, 
     Table2 t2 
WHERE 
     t1.ID = t2.ID;

MySQL

UPDATE 
     Table1 t1, 
     Table2 t2
SET 
     t1.column = t2.column 
WHERE
     t1.ID = t2.ID;

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

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
)

这是Mysql和Maria DB最简单和最好的

UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id

注意:如果您遇到以下错误基于您的Mysql/Maria DB版本“错误代码:1175。您正在使用安全更新模式，并且您试图更新一个没有使用KEY列的WHERE的表。要禁用安全模式，请切换首选项中的选项。

然后像这样运行代码

SET SQL_SAFE_UPDATES=0;
UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id

将内容从一个表复制到另一个表的简单方法如下:

UPDATE table2 
SET table2.col1 = table1.col1, 
table2.col2 = table1.col2,
...
FROM table1, table2 
WHERE table1.memberid = table2.memberid

还可以添加条件以复制特定的数据。

总结其他答案，关于如何仅在“匹配存在”时使用来自另一个表的数据更新目标表，有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的变体更可取: