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

User · Answer

For PostgreSQL   UPDATE Sales Import SI SET AccountNumber   RAN AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN LeadID   SI LeadID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  B AccountNumber  --DON T OVERWRITE A VALUE WITH THE SAME VALUE  SELECT   FROM  TMP1

User · Answer

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

User · Answer

Oracle 11g  merge into Sales Import using RetrieveAccountNumber on  Sales Import LeadId   RetrieveAccountNumber LeadId  when matched then update set Sales Import AccountNumber   RetrieveAccountNumber AccountNumber

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For MySql that works fine   UPDATE     Sales Import SI RetrieveAccountNumber RAN SET     SI AccountNumber   RAN AccountNumber WHERE     SI LeadID   RAN LeadID

User · Answer

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

User · Answer

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

User · Answer

For MySql that works fine   UPDATE     Sales Import SI RetrieveAccountNumber RAN SET     SI AccountNumber   RAN AccountNumber WHERE     SI LeadID   RAN LeadID

User · Answer

Oracle 11g  merge into Sales Import using RetrieveAccountNumber on  Sales Import LeadId   RetrieveAccountNumber LeadId  when matched then update set Sales Import AccountNumber   RetrieveAccountNumber AccountNumber

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  B AccountNumber  --DON T OVERWRITE A VALUE WITH THE SAME VALUE  SELECT   FROM  TMP1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For PostgreSQL   UPDATE Sales Import SI SET AccountNumber   RAN AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN LeadID   SI LeadID

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to select

Examples related to join

Examples related to sql-update