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

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;

Generic answer for future developers.


<h1>SQL Server</h1>

UPDATE
t1
SET
t1.column = t2.column
FROM
Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id;

<h1>Oracle (and SQL Server)</h1>

UPDATE
t1
SET
t1.colmun = t2.column
FROM
Table1 t1,
Table2 t2
WHERE
t1.ID = t2.ID;

<h1>MySQL</h1>

UPDATE
Table1 t1,
Table2 t2
SET
t1.column = t2.column
WHERE
t1.ID = t2.ID;

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

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

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.

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

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

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.

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

Oracle 11g

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