By Boerseun


2008-10-22 07:14:03 8 Comments

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

22 comments

@Maurico Bello 2019-04-24 04:14:53

In case the tables are in different database. (SQLserver)

update database1..Ciudad
set CiudadDistrito=c2.CiudadDistrito

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

@saman samadi 2019-04-23 06:30:11

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  ;

@Bruno 2019-01-21 16:40:30

Oracle 11g

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

@Abhimanyu 2018-09-07 12:49:12

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

@Jon 2018-10-23 20:16:30

Possibly a duplicate of stackoverflow.com/a/2101278/2350083

@SharpShade 2018-11-14 15:32:42

Worked like a charm. Thanks!

@Gil Baggio 2017-11-12 16:34:09

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.

@Shaw 2018-01-10 04:10:35

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 

@jakentus 2015-05-14 09:30:28

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

@Dr Inner Join 2016-06-12 20:54:06

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.

@SharpShade 2018-11-14 15:35:44

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.

@pacreely 2016-12-13 23:46:41

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

@Oriel.F 2016-11-14 12:58:49

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

@Tigerjz32 2016-08-29 17:59:14

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;

@gordon 2017-04-25 15:58:55

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

@Mark S. Rasmussen 2008-10-22 07:19:54

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;

@Tom H 2008-10-22 20:38:06

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 2010-04-27 17:52:37

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

@Christian Ammer 2010-07-22 12:56:23

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

@Mark S. Rasmussen 2011-08-30 12:39:44

You can't use a table alias in the update clause. I have however updated the sample to reference the updated table directly.

@Edd 2012-02-22 15:10:25

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 2013-11-29 16:08:49

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

@Stephen Lloyd 2014-07-03 15:50:07

@Gutti At least in simple cases, the explain plan is the same: Table scans -> hash match -> sort -> update

@Mr. Polywhirl 2016-03-23 15:22:29

@Edd: I added your MySQL solution to the answer.

@DCShannon 2016-10-19 18:42:45

@MarkS.Rasmussen You actually can use an alias in both the update and set clauses. "update alias set alias.column = alias2.column from table1 as alias join table2 as alias2 ..." works.

@Jugali Lakota 2016-11-09 16:26:03

You could also use JOIN in MySQL UPDATE Sales_Import JOIN RetrieveAccountNumber ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber

@Luke Alderton 2016-12-13 15:59:48

This works better than the answer below as the INNER JOIN solves the 'must declare the scalar variable' error when using a user defined table type as a parameter for a stored procedure.

@Keutelvocht 2018-03-08 14:55:22

Set should be under the join on

@user734028 2019-08-22 07:57:40

why doesnt this comment appear as the top answer, the first one to see, cant the algorithm adjust this?

@Mike D Wakelyn 2019-09-03 09:43:33

This just saved my day! Thanks.

@CG_DEV 2015-04-02 15:27:50

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
    )

@petter 2014-07-16 19:55:13

For PostgreSQL:

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

@Martin Smith 2012-02-11 15:13:15

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.

@Jakub Januszkiewicz 2012-06-28 06:16:38

Heads up for MERGE.

@Tony Ashworth 2012-10-12 15:13:34

I never knew about the merge syntax. It's so much cleaner than Update + Join.

@AFract 2013-12-06 09:37:07

+1 for reporting of MERGE SQL Server's implementation

@InfantPro'Aravind' 2014-04-29 13:07:01

Thanks a lot Martin! I had been searching this since many days finally got it working :)

@Charles Clayton 2015-06-17 16:26:12

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 2016-10-27 16:20:29

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 2017-11-08 15:02:41

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 2017-12-22 19:17:07

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

@Martin Smith 2017-12-24 11:29:39

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

@jkp1187 2018-05-11 17:55:01

@MartinSmith For people with limited experience, who happened upon this page thanks to results from a search engine, it's non-zero.

@Shivkant 2010-01-20 12:26:50

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.

@Sirentec 2016-10-24 15:25:53

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 2017-02-02 12:56:16

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

@NCP 2012-12-06 12:24:29

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 2012-06-04 11:55:41

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

@marsanvi 2012-02-13 16:40:03

For MySql that works fine:

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

@Boerseun 2008-10-22 08:07:41

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)  

@Tom H 2008-10-22 20:40:41

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 2012-09-17 20:18:23

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 2018-07-09 07:47:47

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

@Kjell Andreassen 2009-04-29 18:32:22

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)

@Georg Schölly 2010-09-07 15:31:30

Why is the WHERE EXISTS required?

@Kjell Andreassen 2011-01-07 17:43:04

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 2018-07-09 19:56:22

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

@Bhavin Thummar 2019-02-13 11:51:55

@KjellAndreassen You have solved my problem. Thanks for your code.

@Vinko Vrsalovic 2008-10-22 07:21:53

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

Related Questions

Sponsored Content

29 Answered Questions

47 Answered Questions

37 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2570358 View
  • 1735 Score
  • 29 Answer
  • Tags:   sql duplicates

12 Answered Questions

[SOLVED] MySQL - UPDATE query based on SELECT Query

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

10 Answered Questions

[SOLVED] Update a table using JOIN in SQL Server?

15 Answered Questions

[SOLVED] Select n random rows from SQL Server table

  • 2009-05-11 16:19:11
  • John M Gant
  • 337070 View
  • 291 Score
  • 15 Answer
  • Tags:   sql sql-server random

Sponsored Content