sqlguy-736318 (4/4/2013)
What I'm suggesting is that table #a would have 2 rows with the same company. For example:'Microsoft', 'Seattle'
'Microsoft', 'Portland'
In this scenario, row #1 would get updated but row #2 would not.
That's incorrect, you must be observing something different. Using sample script from above, try this:
create table #a (DomainName varchar(25), OfficeName varchar(25));
create table #b (DomainName varchar(25), OfficeName varchar(25));
insert into #a
values
('microsoft', 'seattle'),
('microsoft', 'Reading'),
('dell', 'huston');
insert into #b
values
('microsoft', 'seattle, wa'),
('dell', 'huston, tx');
select * from #a;
update a
set OfficeName = b.OfficeName
from #a a
inner join #b b on b.DomainName = a.DomainName;
select * from #a;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden