我有一个有帐号和卡号的数据库。我将这些匹配到一个文件,以将任何卡号更新为帐号,这样我只使用帐号。

我创建了一个将表链接到帐户/卡数据库的视图,以返回table ID和相关的帐号,现在我需要更新那些ID与account number匹配的记录。

这是Sales_Import表,其中的帐号字段需要更新:

LeadID AccountNumber
147 5807811235
150 5807811326
185 7006100100007267039

这是RetrieveAccountNumber表,我需要从这里更新:

LeadID AccountNumber
147 7006100100007266957
150 7006100100007267039

我尝试了下面的方法,但到目前为止运气都不佳:

UPDATE [Sales_Lead].[dbo].[Sales_Import] 
SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                RetrieveAccountNumber.LeadID) 

它将卡号更新为帐号,但是帐号被NULL替换


当前回答

下面是在SQL Server中对我有效的方法:

UPDATE [AspNetUsers] SET

[AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId],
[AspNetUsers].[Name] = [UserProfile].[Name]

FROM [AspNetUsers], [UserProfile]
WHERE [AspNetUsers].[Id] = [UserProfile].[Id];

其他回答

使用下面的查询块根据ID更新Table1和Table2:

UPDATE Sales_Import, RetrieveAccountNumber 
SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber 
where Sales_Import.LeadID = RetrieveAccountNumber.LeadID;

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

Oracle 11 g

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

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

UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] = 
RetrieveAccountNumber.AccountNumber 
FROM RetrieveAccountNumber 
WHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID

我相信一个带JOIN的UPDATE FROM会有帮助:

MS SQL

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

MySQL和MariaDB

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

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

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)