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 with 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 gets replaced by
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 and MariaDB
UPDATE Sales_Import SI, RetrieveAccountNumber RAN SET SI.AccountNumber = RAN.AccountNumber WHERE SI.LeadID = RAN.LeadID;
Read more... Read less...
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.
Generic answer for future developers.
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;
UPDATE Table1 t1, Table2 t2 SET t1.column = t2.column WHERE t1.ID = t2.ID;
UPDATE Sales_Import SI SET AccountNumber = RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID;
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