SQL Update from One Table to Another Based on a ID Match

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.

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 the 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|


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)

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

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

I had the same problem with <code>foo.new</code> being set to <code>null</code> for rows of <code>foo</code> that had no matching key in <code>bar</code>. I did something like this in Oracle:

<pre>
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)
</pre>

Thanks for the responses. I found a solution tho.

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)

For MySql that works fine:

UPDATE
Sales_Import SI,RetrieveAccountNumber RAN
SET
SI.AccountNumber = RAN.AccountNumber
WHERE
SI.LeadID = RAN.LeadID

I thought this is a simple example might someone get it easier,

DECLARE @TB1 TABLE
(
No Int
,Name NVarchar(50)
)

DECLARE @TB2 TABLE
(
No Int
,Name NVarchar(50)
)

INSERT INTO @TB1 VALUES(1,'asdf');
INSERT INTO @TB1 VALUES(2,'awerq');


INSERT INTO @TB2 VALUES(1,';oiup');
INSERT INTO @TB2 VALUES(2,'lkjhj');

SELECT * FROM @TB1

UPDATE @TB1 SET Name =S.Name
FROM @TB1 T
INNER JOIN @TB2 S
ON S.No = T.No

SELECT * FROM @TB1

update within the same table:


DECLARE @TB1 TABLE
(
No Int
,Name NVarchar(50)
,linkNo int
)

DECLARE @TB2 TABLE
(
No Int
,Name NVarchar(50)
,linkNo int
)

INSERT INTO @TB1 VALUES(1,'changed person data', 0);
INSERT INTO @TB1 VALUES(2,'old linked data of person', 1);

INSERT INTO @TB2 SELECT * FROM @TB1 WHERE linkNo = 0


SELECT * FROM @TB1
SELECT * FROM @TB2


UPDATE @TB1
SET Name = T2.Name
FROM @TB1 T1
INNER JOIN @TB2 T2 ON T2.No = T1.linkNo

SELECT * FROM @TB1

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

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

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

For SQL Server 2008 + Using `MERGE` rather than the proprietary `UPDATE ... FROM` syntax has some appeal.

As well as being standard SQL and thus more portable it also will raise an error in the event of there being multiple joined rows on the source side (and thus multiple possible different values to use in the update) rather than having the final result be undeterministic.

MERGE INTO Sales_Import
USING RetrieveAccountNumber
ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID
WHEN MATCHED THEN
UPDATE
SET AccountNumber = RetrieveAccountNumber.AccountNumber;

Unfortunately the choice of which to use may not come down purely to preferred style however. The implementation of `MERGE` in SQL Server has been afflicted with various bugs. Aaron Bertrand has compiled a list of [the reported ones here][1].


[1]:

[To see links please register here]

For PostgreSQL:

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