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:

And this is the RetrieveAccountNumber table, where I need to update from:

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

27 Answers

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; 

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.

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; 

For PostgreSQL:

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 

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) 

Here's what worked for me in SQL Server:

UPDATE [AspNetUsers] SET [AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId], [AspNetUsers].[Name] = [UserProfile].[Name] FROM [AspNetUsers], [UserProfile] WHERE [AspNetUsers].[Id] = [UserProfile].[Id]; 

For MySql that works fine:

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

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) 

In case the tables are in a different databases. (MSSQL)

update database1..Ciudad set CiudadDistrito=c2.CiudadDistrito FROM database1..Ciudad c1 inner join database2..Ciudad c2 on c2.CiudadID=c1.CiudadID 

Use the following block of query to update Table1 with Table2 based on ID:

UPDATE Sales_Import, RetrieveAccountNumber SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber where Sales_Import.LeadID = RetrieveAccountNumber.LeadID; 

This is the easiest way to tackle this problem.

MS Sql

UPDATE c4 SET Price=cp.Price*p.FactorRate FROM TableNamea_A c4 inner join TableNamea_B p on c4.Calcid=p.calcid inner join TableNamea_A cp on c4.Calcid=cp.calcid WHERE c4..Name='MyName'; 

Oracle 11g

 MERGE INTO TableNamea_A u using ( SELECT c4.TableName_A_ID,(cp.Price*p.FactorRate) as CalcTot FROM TableNamea_A c4 inner join TableNamea_B p on c4.Calcid=p.calcid inner join TableNamea_A cp on c4.Calcid=cp.calcid WHERE p.Name='MyName' ) rt on (u.TableNamea_A_ID=rt.TableNamea_B_ID) WHEN MATCHED THEN Update set Price=CalcTot ; 

The below SQL someone suggested, does NOT work in SQL Server. This syntax reminds me of my old school class:

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

All other queries using NOT IN or NOT EXISTS are not recommended. NULLs show up because OP compares entire dataset with smaller subset, then of course there will be matching problem. This must be fixed by writing proper SQL with correct JOIN instead of dodging problem by using NOT IN. You might run into other problems by using NOT IN or NOT EXISTS in this case.

My vote for the top one, which is conventional way of updating a table based on another table by joining in SQL Server. Like I said, you cannot use two tables in same UPDATE statement in SQL Server unless you join them first.

update from one table to another table on id matched

UPDATE TABLE1 t1, TABLE2 t2 SET t1.column_name = t2.column_name WHERE t1.id = t2.id; 

This is the easiest and best have seen for Mysql and Maria DB

UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id 

Note: If you encounter the following error based on your Mysql/Maria DB version "Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences"

Then run the code like this

SET SQL_SAFE_UPDATES=0; UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id 

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 

it works with postgresql

UPDATE application SET omts_received_date = ( SELECT date_created FROM application_history WHERE application.id = application_history.application_id AND application_history.application_status_id = 8 ); 

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 

try this :

UPDATE Table_A SET Table_A.AccountNumber = Table_B.AccountNumber , FROM dbo.Sales_Import AS Table_A INNER JOIN dbo.RetrieveAccountNumber AS Table_B ON Table_A.LeadID = Table_B.LeadID WHERE Table_A.LeadID = Table_B.LeadID 

MYSQL (This is my preferred way for restoring all specific column reasonId values, based on primary key id equivalence)

UPDATE `site` AS destination INNER JOIN `site_copy` AS backupOnTuesday ON backupOnTuesday.`id` = destination.`id` SET destdestination.`reasonId` = backupOnTuesday.`reasonId` 

Oracle 11g

merge into Sales_Import using RetrieveAccountNumber on (Sales_Import.LeadId = RetrieveAccountNumber.LeadId) when matched then update set Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber; 

For Oracle SQL try using alias

UPDATE Sales_Lead.dbo.Sales_Import SI SET SI.AccountNumber = (SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID); 

I'd like to add one extra thing.

Don't update a value with the same value, it generates extra logging and unnecessary overhead. See example below - it will only perform the update on 2 records despite linking on 3.

DROP TABLE #TMP1 DROP TABLE #TMP2 CREATE TABLE #TMP1(LeadID Int,AccountNumber NVarchar(50)) CREATE TABLE #TMP2(LeadID Int,AccountNumber NVarchar(50)) INSERT INTO #TMP1 VALUES (147,'5807811235') ,(150,'5807811326') ,(185,'7006100100007267039'); INSERT INTO #TMP2 VALUES (147,'7006100100007266957') ,(150,'7006100100007267039') ,(185,'7006100100007267039'); UPDATE A SET A.AccountNumber = B.AccountNumber FROM #TMP1 A INNER JOIN #TMP2 B ON A.LeadID = B.LeadID WHERE A.AccountNumber <> B.AccountNumber --DON'T OVERWRITE A VALUE WITH THE SAME VALUE SELECT * FROM #TMP1 

ORACLE

use

UPDATE suppliers SET supplier_name = (SELECT customers.customer_name FROM customers WHERE customers.customer_id = suppliers.supplier_id) WHERE EXISTS (SELECT customers.customer_name FROM customers WHERE customers.customer_id = suppliers.supplier_id); 

This will allow you to update a table based on the column value not being found in another table.

UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN ( SELECT * FROM ( SELECT table1.id FROM table1 LEFT JOIN table2 ON ( table2.column = table1.column ) WHERE table1.column = 'some_expected_val' AND table12.column IS NULL ) AS Xalias ) 

This will update a table based on the column value being found in both tables.

UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN ( SELECT * FROM ( SELECT table1.id FROM table1 JOIN table2 ON ( table2.column = table1.column ) WHERE table1.column = 'some_expected_val' ) AS Xalias ) 

If above answers not working for you try this

Update Sales_Import A left join RetrieveAccountNumber B on A.LeadID = B.LeadID Set A.AccountNumber = B.AccountNumber where A.LeadID = B.LeadID