我相信UPDATE FROM和JOIN会有所帮助：

MSSQL

UPDATESales_ImportSETSales_Import.AccountNumber = RAN.AccountNumberFROMSales_Import SIINNER JOINRetrieveAccountNumber RANONSI.LeadID = RAN.LeadID;

MySQL和MariaDB

UPDATESales_Import SI,RetrieveAccountNumber RANSETSI.AccountNumber = RAN.AccountNumberWHERESI.LeadID = RAN.LeadID;

似乎你正在使用MSSQL，然后，如果我没记错的话，它是这样做的：

UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] =RetrieveAccountNumber.AccountNumberFROM RetrieveAccountNumberWHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID

谢谢你的回复。我找到了一个解决方案。

UPDATE Sales_ImportSET    AccountNumber = (SELECT RetrieveAccountNumber.AccountNumberFROM   RetrieveAccountNumberWHERE  Sales_Import.leadid =RetrieveAccountNumber.LeadID)WHERE Sales_Import.leadid = (SELECT  RetrieveAccountNumber.LeadIDFROM   RetrieveAccountNumberWHERE  Sales_Import.leadid = RetrieveAccountNumber.LeadID)

我在bar中没有匹配键的foo行将foo.new设置为null时遇到了同样的问题。我在Oracle中做了这样的事情：

update fooset    foo.new = (select bar.newfrom barwhere foo.key = bar.key)where exists (select 1from barwhere foo.key = bar.key)

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

UPDATE table2SET table2.col1 = table1.col1,table2.col2 = table1.col2,...FROM table1, table2WHERE table1.memberid = table2.memberid

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

SQLServer 2008+使用MERGE而不是专有的UPDATE ... FROM语法有一定的吸引力。

除了是标准的SQL因此更具可移植性，它还会在源端有多个连接的行（因此在更新中使用多个可能的不同值）而不是最终结果不确定的情况下引发错误。

MERGE INTO Sales_ImportUSING RetrieveAccountNumberON Sales_Import.LeadID = RetrieveAccountNumber.LeadIDWHEN MATCHED THENUPDATESET AccountNumber = RetrieveAccountNumber.AccountNumber;

不幸的是，选择使用哪一种可能并不完全取决于首选的样式。SQLServer中的MERGE实现受到各种错误的困扰。Aaron Bertrand编制了一份这里报道的列表。

对于MySql工作正常：

UPDATESales_Import SI,RetrieveAccountNumber RANSETSI.AccountNumber = RAN.AccountNumberWHERESI.LeadID = RAN.LeadID

我想这是一个简单的例子，也许有人会更容易，

        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.NameFROM @TB1 TINNER JOIN @TB2 SON S.No = T.No
SELECT * FROM @TB1

在同一个表中更新：

  DECLARE @TB1 TABLE(No Int,Name NVarchar(50),linkNo int)
DECLARE @TB2 TABLE(No Int,Name NVarchar(50),linkNo int)
INSERT INTO @TB1 VALUES(1,'changed person data',  0);INSERT INTO @TB1 VALUES(2,'old linked data of person', 1);
INSERT INTO @TB2 SELECT * FROM @TB1 WHERE linkNo = 0

SELECT * FROM @TB1SELECT * FROM @TB2

UPDATE @TB1SET Name = T2.NameFROM        @TB1 T1INNER JOIN  @TB2 T2 ON T2.No = T1.linkNo
SELECT * FROM @TB1

对于PostgreSQL：

UPDATE Sales_Import SISET AccountNumber = RAN.AccountNumberFROM RetrieveAccountNumber RANWHERE RAN.LeadID = SI.LeadID;

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

UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN (SELECT *FROM (SELECT table1.idFROM  table1LEFT 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.idFROM  table1JOIN table2 ON ( table2.column = table1.column )WHERE table1.column = 'some_expected_val') AS Xalias)

它与postgreSQL一起工作

UPDATE applicationSET omts_received_date = (SELECTdate_createdFROMapplication_historyWHEREapplication.id = application_history.application_idAND application_history.application_status_id = 8);

下面的SQL有人建议，在SQL服务器中不起作用。这个语法让我想起了我以前的学校课程：

UPDATE table2SET table2.col1 = table1.col1,table2.col2 = table1.col2,...FROM table1, table2WHERE table1.memberid = table2.memberid

不推荐使用NOT IN或NOT EXISTS进行其他查询。由于OP会将整个数据集与更小的子集进行比较，因此会出现NULL，因此当然会出现匹配问题。必须通过使用正确的JOIN编写正确的SQL来解决此问题，而不是使用NOT IN来规避问题。在这种情况下，使用NOT IN或NOT EXISTS可能会遇到其他问题。

我投票支持顶部，这是通过加入SQLServer来更新基于另一个表的表的传统方式。就像我说的，除非你先加入它们，否则你不能在SQLServer的同一个UPDATE语句中使用两个表。

面向未来开发人员的通用答案。

SQL服务器

UPDATEt1SETt1.column = t2.columnFROMTable1 t1INNER JOIN Table2 t2ON t1.id = t2.id;

Oracle（和SQL服务器）

UPDATEt1SETt1.colmun = t2.columnFROMTable1 t1,Table2 t2WHEREt1.ID = t2.ID;

mysql

UPDATETable1 t1,Table2 t2SETt1.column = t2.columnWHEREt1.ID = t2.ID;

试试这个：

UPDATETable_ASETTable_A.AccountNumber = Table_B.AccountNumber ,FROMdbo.Sales_Import AS Table_AINNER JOIN dbo.RetrieveAccountNumber AS Table_BON Table_A.LeadID = Table_B.LeadIDWHERETable_A.LeadID = Table_B.LeadID

我想补充一件事。

不要使用相同的值更新值，这会产生额外的日志记录和不必要的开销。请参阅下面的示例-尽管链接了3条记录，但它只会对2条记录执行更新。

DROP TABLE #TMP1DROP TABLE #TMP2CREATE TABLE #TMP1(LeadID Int,AccountNumber NVarchar(50))CREATE TABLE #TMP2(LeadID Int,AccountNumber NVarchar(50))
INSERT INTO #TMP1 VALUES(147,'5807811235'),(150,'5807811326'),(185,'7006100100007267039');
INSERT INTO #TMP2 VALUES(147,'7006100100007266957'),(150,'7006100100007267039'),(185,'7006100100007267039');
UPDATE ASET A.AccountNumber = B.AccountNumberFROM#TMP1 AINNER JOIN #TMP2 BONA.LeadID = B.LeadIDWHEREA.AccountNumber <> B.AccountNumber  --DON'T OVERWRITE A VALUE WITH THE SAME VALUE
SELECT * FROM #TMP1

使用以下查询块根据ID使用Table2更新Table1：

UPDATE Sales_Import, RetrieveAccountNumberSET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumberwhere Sales_Import.LeadID = RetrieveAccountNumber.LeadID;

这是解决这个问题的最简单的方法。

如果上面的答案不适合你试试这个

Update Sales_Import A left join RetrieveAccountNumber B on A.LeadID = B.LeadIDSet A.AccountNumber = B.AccountNumberwhere A.LeadID = B.LeadID

以下是我在SQL服务器中的工作：

UPDATE [AspNetUsers] SET
[AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId],[AspNetUsers].[Name] = [UserProfile].[Name]
FROM [AspNetUsers], [UserProfile]WHERE [AspNetUsers].[Id] = [UserProfile].[Id];

oracle11g

merge into Sales_Importusing RetrieveAccountNumberon (Sales_Import.LeadId = RetrieveAccountNumber.LeadId)when matched then update set Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber;

msql

UPDATE  c4 SET Price=cp.Price*p.FactorRate FROM TableNamea_A c4inner join TableNamea_B p on c4.Calcid=p.calcidinner join TableNamea_A cp on c4.Calcid=cp.calcidWHERE c4..Name='MyName';

oracle11g

        MERGE INTO  TableNamea_A uusing(SELECT c4.TableName_A_ID,(cp.Price*p.FactorRate) as CalcTotFROM TableNamea_A c4inner join TableNamea_B p on c4.Calcid=p.calcidinner join TableNamea_A cp on c4.Calcid=cp.calcidWHERE p.Name='MyName')  rton (u.TableNamea_A_ID=rt.TableNamea_B_ID)WHEN MATCHED THENUpdate set Price=CalcTot  ;

如果表位于不同的数据库中。（MSSQL）

update database1..Ciudadset CiudadDistrito=c2.CiudadDistrito
FROM database1..Ciudad c1inner joindatabase2..Ciudad c2 on c2.CiudadID=c1.CiudadID

在ID匹配时从一个表更新到另一个表

UPDATETABLE1 t1,TABLE2 t2SETt1.column_name = t2.column_nameWHEREt1.id = t2.id;

MYSQL（这是我首选的恢复所有特定列reasonId值的方法，基于主键id等价）

UPDATE `site` AS destinationINNER JOIN `site_copy` AS backupOnTuesdayON backupOnTuesday.`id` = destination.`id`SET destdestination.`reasonId` = backupOnTuesday.`reasonId`

这是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 suppliersSET supplier_name = (SELECT customers.customer_nameFROM customersWHERE customers.customer_id = suppliers.supplier_id)WHERE EXISTS (SELECT customers.customer_nameFROM customersWHERE customers.customer_id = suppliers.supplier_id);

对于OracleSQL尝试使用别名

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

更新table1 dpm设置col1=dpu.col1从table2 dpu其中dpm.parameter_master_id=dpu.parameter_master_id；