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

我创建了一个将表链接到帐户/卡数据库的视图,以返回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];

其他回答

更新表1 DPM set col1 = dpu。Col1 from table2 dpu其中dpm。Parameter_master_id = dpu.parameter_master_id;

对于Oracle SQL,请尝试使用别名

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

我对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)

我相信一个带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;

根据匹配的id从一个表更新到另一个表

UPDATE 
     TABLE1 t1, 
     TABLE2 t2
SET 
     t1.column_name = t2.column_name 
WHERE
     t1.id = t2.id;