基于 ID 匹配的从一个表到另一个表的 SQL 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/224732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL update from one Table to another based on a ID match
提问by Mark S. Rasmussen
I have a database with account numbersand card numbers. I match these to a file to updateany card numbers to the account number, so that I am only working with account numbers.
我有一个带有account numbers和的数据库card numbers。我将这些文件与update任何卡号与帐号匹配，这样我就只使用帐号。
I created a view linking the table to the account/card database to return the Table IDand the related account number, and now I need to update those records where the ID matches with the Account Number.
我创建了一个视图，将表链接到帐户/卡数据库以返回Table ID和相关的帐号，现在我需要更新 ID 与帐号匹配的那些记录。
This is the Sales_Importtable, where the account numberfield needs to be updated:
这是需要更新字段的Sales_Import表account number：
LeadID AccountNumber
147 5807811235
150 5807811326
185 7006100100007267039
And this is the RetrieveAccountNumbertable, where I need to update from:
这是RetrieveAccountNumber我需要更新的表格：
LeadID AccountNumber
147 7006100100007266957
150 7006100100007267039
I tried the below, but no luck so far:
我尝试了以下方法，但到目前为止还没有运气：
UPDATE [Sales_Lead].[dbo].[Sales_Import]
SET [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber
FROM RetrieveAccountNumber
WHERE [Sales_Lead].[dbo].[Sales_Import]. LeadID =
RetrieveAccountNumber.LeadID)
It updates the card numbers to account numbers, but the account numbers gets replaced by NULL
它将卡号更新为帐号，但帐号被替换为 NULL
回答by Mark S. Rasmussen
I believe an UPDATE FROMwith a JOINwill help:
我相信UPDATE FROM有一个JOIN会帮助：
MS SQL
微软SQL
UPDATE
Sales_Import
SET
Sales_Import.AccountNumber = RAN.AccountNumber
FROM
Sales_Import SI
INNER JOIN
RetrieveAccountNumber RAN
ON
SI.LeadID = RAN.LeadID;
MySQL and MariaDB
MySQL 和 MariaDB
UPDATE
Sales_Import SI,
RetrieveAccountNumber RAN
SET
SI.AccountNumber = RAN.AccountNumber
WHERE
SI.LeadID = RAN.LeadID;
回答by Shivkant
The simple Way to copy the content from one table to other is as follow:
将内容从一张表复制到另一张表的简单方法如下：
UPDATE table2
SET table2.col1 = table1.col1,
table2.col2 = table1.col2,
...
FROM table1, table2
WHERE table1.memberid = table2.memberid
You can also add the condition to get the particular data copied.
您还可以添加条件以复制特定数据。
回答by Martin Smith
For SQL Server 2008 + Using MERGErather than the proprietary UPDATE ... FROMsyntax has some appeal.
对于 SQL Server 2008 + 使用MERGE而不是专有UPDATE ... FROM语法有一些吸引力。
As well as being standard SQL and thus more portable it also will raise an error in the event of there being multiple joined rows on the source side (and thus multiple possible different values to use in the update) rather than having the final result be undeterministic.
除了是标准 SQL 并因此更具可移植性之外，如果源端有多个连接的行（因此在更新中使用多个可能的不同值），它也会引发错误，而不是最终结果不确定.
MERGE INTO Sales_Import
USING RetrieveAccountNumber
ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID
WHEN MATCHED THEN
UPDATE
SET AccountNumber = RetrieveAccountNumber.AccountNumber;
Unfortunately the choice of which to use may not come down purely to preferred style however. The implementation of MERGEin SQL Server has been afflicted with various bugs. Aaron Bertrand has compiled a list of the reported ones here.
不幸的是，选择使用哪种风格可能并不完全取决于首选风格。执行MERGESQL Server中已患各种错误。Aaron Bertrand 在这里编制了一份报告清单。
回答by Tigerjz32
Generic answer for future developers.
未来开发人员的通用答案。
SQL Server
数据库服务器
UPDATE
t1
SET
t1.column = t2.column
FROM
Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id;
Oracle (and SQL Server)
Oracle（和 SQL Server）
UPDATE
t1
SET
t1.colmun = t2.column
FROM
Table1 t1,
Table2 t2
WHERE
t1.ID = t2.ID;
MySQL
MySQL
UPDATE
Table1 t1,
Table2 t2
SET
t1.column = t2.column
WHERE
t1.ID = t2.ID;
回答by petter
For PostgreSQL:
对于 PostgreSQL：
UPDATE Sales_Import SI
SET AccountNumber = RAN.AccountNumber
FROM RetrieveAccountNumber RAN
WHERE RAN.LeadID = SI.LeadID;
回答by Vinko Vrsalovic
Seems you are using MSSQL, then, if I remember correctly, it is done like this:
似乎您正在使用 MSSQL，然后，如果我没记错的话，它是这样完成的：
UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] =
RetrieveAccountNumber.AccountNumber
FROM RetrieveAccountNumber
WHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID
回答by Kjell Andreassen
I had the same problem with foo.newbeing set to nullfor rows of foothat had no matching key in bar. I did something like this in Oracle:
我有同样的问题foo.new被设定null为行foo是曾在没有匹配的关键bar。我在 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)
回答by marsanvi
For MySql that works fine:
对于工作正常的 MySql：
UPDATE
Sales_Import SI,RetrieveAccountNumber RAN
SET
SI.AccountNumber = RAN.AccountNumber
WHERE
SI.LeadID = RAN.LeadID
回答by Abhimanyu
Here's what worked for me in SQL Server:
以下是在 SQL Server 中对我有用的内容：
UPDATE [AspNetUsers] SET
[AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId],
[AspNetUsers].[Name] = [UserProfile].[Name]
FROM [AspNetUsers], [UserProfile]
WHERE [AspNetUsers].[Id] = [UserProfile].[Id];
回答by Abhimanyu
Thanks for the responses. I found a solution tho.
感谢您的回复。我找到了一个解决方案。
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)
