SQL inner join two tables from separate databases and update one of them

I have the following SQL query:

UPDATE db1.dbo.oitems
SET f2 = oo.f2,
f3 = oo.f3,
f4 = oo.f4
FROM db1.dbo.oitems o
       INNER JOIN db2.dbo.oitems oo 
               ON o.orderid = oo.orderid

Each table is in a different database and they have identical columns but different data with some matches in id but not in data. I simply want to set values for the columns f2,f3,f4 in the table I want to update to the values in the second table if they have the same orderid. The above command keeps saying 0 rows affected, so what’s wrong with my logic?

  • SQL how many records start with the same letter
  • Update button keeps updating on click of Refresh in IE
  • SQL Server 2008 Empty String vs. Space
  • Variables scope which are defined within a while block in stored procedures - SQl Server
  • SSRS Report is throwing runtime error from Report Manager
  • ServiceStack OrmLite with multiple Database Servers
  • 2 Solutions collect form web for “SQL inner join two tables from separate databases and update one of them”

    You have two options to solve this. The first was described by Gordon Linoff in another answer to this thread. The second looks like this:

    UPDATE o
    SET f2 = oo.f2,
    f3 = oo.f3,
    f4 = oo.f4,
    FROM db1.dbo.oitems o
    INNER JOIN db2.dbo.oitems oo 
    ON o.orderid = oo.orderid;
    

    I prefer the second for several reasons. One reason is that you can replace UPDATE o SET with SELECT to get to an executable SELECT statement. Another is that the intend is not hidden as it is in the first option. You also can use LEFT OUTER or other join types that the first option does not give you.

    For a more in-depth explanation of all this check out
    http://sqlity.net/en/1595/a-join-a-day-update-delete/
    as well as the rest of the http://sqlity.net/en/1146/a-join-a-day-introduction/ series.

    The query in your question has a syntax error, so I’m surprised that it runs (the last comma in the set clause). I would write the query as:

    UPDATE db1.dbo.oitems
        SET f2 = oo.f2,
            f3 = oo.f3,
            f4 = oo.f4
        FROM db2.dbo.oitems oo 
        WHERE oitems.orderid = oo.orderid
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.