SQLTeam.com | Weblogs | Forums

T-Sql to populate a field from values of another table


Hi all,
I am fairly new to T-Sql, so please be patient if this is a fairly dumb question:
basically, I have a table where a column has to be filled based on another table.
Here we go:
SELECT DISTINCT(personID) FROM dbo.tbl1 WHERE location = ''
SELECT location FROM dbo.tbl2 WHERE personID = @resultfromtbl1 <<-??
UPDATE dbo.tbl1 SET location=@resultfromtbl2 WHERE personID= <<-??

Now I dont know where to store the resultsets, how to tell T-SQL to loop on query2 and then loop on update statement for each resultset there....

Can anyone please point me to the right direction?


update t
set location = t2.location
from tbl1 t
join tbl2 t2
on t.personid = t2.personid
where t.location = ''


Wonderful! Thanks a lot!
Worked like a charm...


It would be better if you only store the location of a person once in the database ("Normalisation")

What happens it the Location for a person is different in [tbl1] and [tbl2] - which value do you "trust" more the other value?