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