基于ID匹配从一个表到另一个表的SQL更新

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

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

当前回答

甲骨文

use

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

2022-01-28 15:14:07

其他回答

甲骨文

use

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

2022-01-28 15:14:07

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

2009-04-29 18:32:22

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

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)

2008-10-22 08:07:41

以防表在不同的数据库中。(该)

update database1..Ciudad
set CiudadDistrito=c2.CiudadDistrito

FROM database1..Ciudad c1
 inner join 
  database2..Ciudad c2 on c2.CiudadID=c1.CiudadID

2019-04-24 04:14:53

PostgreSQL的:

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

2014-07-16 19:55:13

基于ID匹配从一个表到另一个表的SQL更新

推荐文章

最新文章

标签