SQL update from one Table to another based on a ID match

Question

Hi All

I realy hope someone can help me with this. I have a databse with Account Numbers and card Numbers that I match to a file to update any card numbers to account number so I only work with account numbers. I created a view linking the Table to the Account/Card db to return the Table ID and the related Account number. Now I need to update those records where the ID matches with the Account Number.

Sales_Import where the account number field needs to be updated

LeadID  AccountNumber

147 5807811235
150 5807811326
185 7006100100007267039

RetrieveAccountNumber where I need to update from

LeadID  AccountNumber

147 7006100100007266957
150 7006100100007267039

Tried the below but no luck

UPDATE    [Sales_Lead].[dbo].[Sales_Import]

SET       [AccountNumber]= (SELECT  RetrieveAccountNumber.AccountNumber

      FROM RetrieveAccountNumber 

      where [Sales_Lead].[dbo]

           [Sales_Import].leadid=RetrieveAccountNumber.LeadID)

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

Answer 1

I can't really follow your structure, but I believe what you're looking for is an UPDATE FROM with a join.

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

The above example isn't directly applicable to your structure, but I hope you get my drift.

EDIT: Updated sample to match your structure.

Answer 2

Seems you are using MSSQL, then, if I remember correctly, it is done like this:

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

Answer 3

Thanks for the responses. I found a solution tho.

UPDATE [Sales_Lead].[dbo].[Sales_Import]

SET [AccountNumber]= (SELECT RetrieveAccountNumber.AccountNumber

FROM RetrieveAccountNumber

where [Sales_Lead].[dbo].[Sales_Import].leadid=RetrieveAccountNumber.LeadID)

Where [Sales_Lead].[dbo].[Sales_Import].leadid=(SELECT RetrieveAccountNumber.LeadID

FROM RetrieveAccountNumber

where [Sales_Lead].[dbo].[Sales_Import].leadid=RetrieveAccountNumber.LeadID)

Answer 4

I had the same problem with foo.new being set to null for rows of foo that had no matching key in bar. I did something like this in 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)

Answer 5

The simple Way to copy the content from one table to other is as follow:

UPDATE table2 
SET table2col1 = table1.col1, 
table2col2 = table1.col2,
...
FROM table1, table2 
WHERE table1.memberid = table2.memberid

You can also add the condition to get the particular data copied.