问题描述
我有一个包含帐号
和卡号
的数据库.我将这些匹配到一个文件,以更新
任何卡号到帐号,这样我就只使用帐号.
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;
这篇关于基于 ID 匹配的从一个表到另一个表的 SQL 更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!