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

2021-12-01 00:00:00 join sql-update sql select sql-server

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

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.

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

I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.

这是Sales_Import 表，其中account number 字段需要更新:

This is the Sales_Import table, where the account number field needs to be updated:

LeadID  AccountNumber
147         5807811235
150         5807811326
185         7006100100007267039

这是 RetrieveAccountNumber 表，我需要从中更新:

And this is the RetrieveAccountNumber table, where I need to update from:

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)

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

It updates the card numbers to account numbers, but the account numbers gets replaced by NULL

推荐答案

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

I believe an UPDATE FROM with a JOIN will help:

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;

相关文章