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

Question

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?

Answer 1

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.

Answer 2

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