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

Question

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:

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 get replaced by NULL

Mr. Polywhirl · Accepted Answer · 2016-03-23 15:22:06Z

1741

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;

edited Mar 23, 2016 at 15:22

Mr. Polywhirl

49.1k12 gold badges96 silver badges147 bronze badges

answered Oct 22, 2008 at 7:19

Mark S. Rasmussen

35.7k4 gold badges43 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Tom H Over a year ago

You might want to use the table alias in the UPDATE clause, otherwise it will cause problems if you self join the table at any point.

AaronLS Over a year ago

In the set clause you should change SI.AccountNumber to just AccountNumber otherwise it will fail.

Christian Ammer Over a year ago

MS-Access uses a different UPDATE with JOIN Statement. Have a look at: sql-und-xml.de/sql-tutorial/…

Edd Over a year ago

this seems to be fine for mssql but doesn't seem to work in mysql. This seems to do the job though:

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

. Slightly off topic but may be helpful

Gutti Over a year ago

I think there is no need for the inner join. Vonki solution below works: UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] = RetrieveAccountNumber.AccountNumber FROM RetrieveAccountNumber WHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID

|

cvsguimaraes · Accepted Answer · 2013-07-15 06:01:04Z

352

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.

edited Jul 15, 2013 at 6:01

cvsguimaraes

13.3k10 gold badges52 silver badges76 bronze badges

answered Jan 20, 2010 at 12:26

Shivkant

4,6251 gold badge22 silver badges16 bronze badges

2 Comments

m0r6aN Over a year ago

This works, but you don't need table2 in the FROM UPDATE table2 SET table2.col1 = table1.col1, table2.col2 = table1.col2, ... FROM table1 WHERE table1.memberid = table2.memberid

Tom Kuschel Over a year ago

This didn't worked, but UPDATE table2, table1 SET table2.col1 = table1.col1, ... WHERE table1.memberid = table2.memberid (mysql and phpmyadmin)

Martin Smith · Accepted Answer · 2013-12-01 14:37:29Z

174

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.

edited Dec 1, 2013 at 14:37

answered Feb 11, 2012 at 15:13

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

7 Comments

Charles Clayton Over a year ago

MERGE is great, but it's worth noting that it will not work if you're using a linked server The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables..

Tony Pulokas Over a year ago

The arguments for using MERGE (including those in the post from sqlblog.com linked above) might be compelling, but one thing to consider might be that according to MSDN: ...MERGE statement works best when the two tables have a complex mixture of matching characteristics...When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements

ruffin Over a year ago

I realize it's dated now, but the linked post from Bertrand says, "However, MERGE originally shipped with several "wrong results" and other bugs... some of which continue to exist even in the early preview releases of SQL Server 2014... I have been recommending that - for now - people stick to their tried and true methods of separate statements." (emph mine) See also his comment on January 26, 2014 - 8:28:58 PM. @MartinSmith -- Any reason to suspect things are better now?

jkp1187 Over a year ago

FWIW, does not work with Access shipped with MS Office 365 Pro Plus.

Martin Smith Over a year ago

@jkp1187 This question is tagged SQL Server. So RE: FWIW - approximately zero.

|

Tigerjz32 · Accepted Answer · 2016-10-23 04:50:42Z

170

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;

edited Oct 23, 2016 at 4:50

answered Aug 29, 2016 at 17:59

Tigerjz32

4,5034 gold badges28 silver badges35 bronze badges

6 Comments

gordon Over a year ago

Of note at least for SQL Server, use the alias rather than the table name in the top update clause (update t1... rather than update Table1...)

AndyH Over a year ago

Does not work in Oracle: ORA-00933: SQL command not properly ended

Tigerjz32 Over a year ago

What is the suggested solution for ORA-00933?

Cuado Over a year ago

the oracle variant did not work for me

OKEEngine Over a year ago

The Oracle SQL answer is obviously wrong, you can only use Merge Into or correlated subquery to achieve this

|

petter · Accepted Answer · 2014-07-16 19:55:13Z

82

For PostgreSQL:

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

answered Jul 16, 2014 at 19:55

petter

1,87219 silver badges23 bronze badges

1 Comment

Karan Arora Over a year ago

The mistake which I am doing is: SET SI.AccountNumber = RAN.AccountNumber. Still, I wonder why it is wrong in postgresql? Can anyone explain?

Vinko Vrsalovic · Accepted Answer · 2008-10-22 07:21:53Z

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

Kjell Andreassen · Accepted Answer · 2009-04-29 18:32:22Z

38

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)

answered Apr 29, 2009 at 18:32

Kjell Andreassen

7636 silver badges11 bronze badges

3 Comments

Georg Schölly Over a year ago

Why is the WHERE EXISTS required?

Kjell Andreassen Over a year ago

Because each row in foo not having a match in bar ended up being null, because the select statement produced null. Hope this was clearer than my first attempt at explaining it.

Basheer AL-MOMANI Over a year ago

check this answer below stackoverflow.com/questions/224732/…

Abhimanyu · Accepted Answer · 2018-09-07 12:49:12Z

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

marsanvi · Accepted Answer · 2012-02-13 16:40:03Z

For MySql that works fine:

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

Martin Smith · Accepted Answer · 2011-05-02 22:58:31Z

24

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)

edited May 2, 2011 at 22:58

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

answered Oct 22, 2008 at 8:07

Boerseun

3 Comments

Tom H Over a year ago

Whether or not the code here works, you should probably look at the other two solutions posted. They are much clearer and much less prone to error as well as almost certainly faster.

pseudocoder Over a year ago

Just a note on this solution, UPDATE...FROM is proprietary therefore, if you cannot use the MERGE statement because you are using SQL 2005 or earlier, this is an ANSI-compliant method of performing updates with a table source in MSSQL. Source: sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/…

Basheer AL-MOMANI Over a year ago

the only solution that works for me because its a standard SQL update statement (UPDATE SET WHERE), thanks alot

Arsen Khachaturyan · Accepted Answer · 2020-04-19 16:58:52Z

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

Gil Baggio · Accepted Answer · 2018-01-24 09:23:08Z

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.

This query is good, but until the development is done, it will block the whole table. can we make any change here, to convert it to a row-based lock only?

saman samadi · Accepted Answer · 2019-04-23 06:30:11Z

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 ;

Vladislav Varslavans · Accepted Answer · 2020-12-21 14:18:44Z

12

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;

edited Dec 21, 2020 at 14:18

Vladislav Varslavans

2,9944 gold badges21 silver badges36 bronze badges

answered Dec 21, 2020 at 13:25

Shilwant Gupta

1691 silver badge4 bronze badges

2 Comments

David Pierson Over a year ago

In SQL Server v18, it does not seem to allow a second table in the UPDATE. It complains about the comma and wants a SET statement. Perhaps this solution works in a different database.

Luis H Cabrejo Over a year ago

On MariaDB and MySQL worked like a charm... it took its time but it searched over 18M records, but did it perfectly. Not only for ID, I used other variables as well such as WHERE t1.lastname = t2.lastname;

Smi · Accepted Answer · 2016-12-14 15:24:12Z

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.

I can only say that in SQL Server 2017 this works perfectly well. Just as a note for future people coming by. No need to join them.

Uladzimir · Accepted Answer · 2022-12-23 11:20:17Z

Summarizing the other answers, there're 4 variants of how to update target table using data from another table only when "match exists"

Query and sub-query:

update si set si.AccountNumber = ( select ran.AccountNumber from RetrieveAccountNumber ran where si.LeadID = ran.LeadID ) from Sales_Import si where exists (select * from RetrieveAccountNumber ran where ran.LeadID = si.LeadID)

Inner join:

update si set si.AccountNumber = ran.AccountNumber from Sales_Import si inner join RetrieveAccountNumber ran on si.LeadID = ran.LeadID

Cross join:

update si set si.AccountNumber = ran.AccountNumber from Sales_Import si, RetrieveAccountNumber ran where si.LeadID = ran.LeadID

Merge:

merge into Sales_Import si using RetrieveAccountNumber ran on si.LeadID = ran.LeadID when matched then update set si.accountnumber = ran.accountnumber;

All variants are more-less trivial and understandable, personally I prefer "inner join" option. But any of them could be used and developer has to select "better option" according to his/her needs

From performance perspective variants with join-s are more preferable:

Successfully used the cross join example. As the source was a backup and the tables had the same names I needed to use alias names but it worked beautifully then. Thanks!

ABODE · Accepted Answer · 2021-03-08 11:55:03Z

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

Faisal · Accepted Answer · 2017-01-02 11:40:10Z

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

NCP · Accepted Answer · 2012-12-06 12:24:29Z

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

user824910 · Accepted Answer · 2012-06-04 12:06:33Z

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

Developer · Accepted Answer · 2016-11-14 12:58:49Z

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

dobrivoje · Accepted Answer · 2021-01-06 09:46:14Z

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`

xXx · Accepted Answer · 2022-05-01 02:38:36Z

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 )

Bruno · Accepted Answer · 2019-01-21 16:40:30Z

Oracle 11g

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

Pasindu Perera · Accepted Answer · 2022-02-22 08:08:03Z

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

pacreely · Accepted Answer · 2016-12-13 23:46:41Z

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

Cuado · Accepted Answer · 2022-01-28 15:14:07Z

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

ankit giri · Accepted Answer · 2022-12-07 09:23:30Z

update table1 dpm set col1 = dpu.col1 from table2 dpu where dpm.parameter_master_id = dpu.parameter_master_id;

Shaw · Accepted Answer · 2018-01-10 04:10:35Z

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

Collectives™ on Stack Overflow

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

29 Answers 29

MS SQL

MySQL and MariaDB

12 Comments

2 Comments

7 Comments

SQL Server

Oracle (and SQL Server)

MySQL

6 Comments

1 Comment

Comments

3 Comments

Comments

Comments

3 Comments

Comments

1 Comment

Comments

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

ORACLE

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

29 Answers 29

MS SQL

MySQL and MariaDB

12 Comments

2 Comments

7 Comments

SQL Server

Oracle (and SQL Server)

MySQL

6 Comments

1 Comment

Comments

3 Comments

Comments

Comments

3 Comments

Comments

1 Comment

Comments

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

ORACLE

Comments

Comments

Comments

Linked

Related