SQL update from one Table to another based on a ID match
Solution 1
I believe an UPDATE FROM
with a JOIN
will help:
MS SQL
UPDATE
Sales_Import
SET
Sales_Import.AccountNumber = RAN.AccountNumber
FROM
Sales_Import SI
INNER JOIN
RetrieveAccountNumber RAN
ON
SI.LeadID = RAN.LeadID;
MySQL and MariaDB
UPDATE
Sales_Import SI,
RetrieveAccountNumber RAN
SET
SI.AccountNumber = RAN.AccountNumber
WHERE
SI.LeadID = RAN.LeadID;
Solution 2
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.
Solution 3
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.
Solution 4
Generic answer for future developers.
SQL Server
UPDATE
t1
SET
t1.column = t2.column
FROM
Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id;
Oracle (and SQL Server)
UPDATE
t1
SET
t1.colmun = t2.column
FROM
Table1 t1,
Table2 t2
WHERE
t1.ID = t2.ID;
MySQL
UPDATE
Table1 t1,
Table2 t2
SET
t1.column = t2.column
WHERE
t1.ID = t2.ID;
Solution 5
For PostgreSQL:
UPDATE Sales_Import SI
SET AccountNumber = RAN.AccountNumber
FROM RetrieveAccountNumber RAN
WHERE RAN.LeadID = SI.LeadID;
Admin
Updated on January 29, 2022Comments
-
Admin over 2 years
I have a database with
account numbers
andcard numbers
. I match these to a file toupdate
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 with the Account Number.This is the
Sales_Import
table, where theaccount 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 gets replaced by
NULL