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

Sql Problem Overview

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 NULL

Sql Solutions

Solution 1 - Sql

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

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

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

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

For PostgreSQL:

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

Solution 6 - Sql

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

Solution 7 - Sql

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)

Solution 8 - Sql

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];

Solution 9 - Sql

For MySql that works fine:

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

Solution 10 - Sql

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)

Solution 11 - Sql

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

Solution 12 - Sql

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.

Solution 13 - Sql

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  ;

Solution 14 - Sql

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.

Solution 15 - Sql

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;

Solution 16 - Sql

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

Solution 17 - Sql

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

Solution 18 - Sql

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
);

Solution 19 - Sql

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

Solution 20 - Sql

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`

Solution 21 - Sql

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

Solution 22 - Sql