UPDATE column from another column two tables away

Question

Not that good with SQL yet but I'm learning.

So I have 3 tables:

tblOne(Id, Type) tblTemp1(Name, Type) tblTemp2(Id, Name)

Basically, I want to update tblOne where the where it's 'Id' matches from tblTemp2, but than that also goes and grabs its 'Type' from tblTemp1 where the 'Name' matches.

Can anyone help?

Answer 1

I would suggest using joins in your update statement.

UPDATE tblOne 
SET tblOne.Type = tblTemp1.Type
FROM tblTemp2
INNER JOIN tblTemp1 ON tblTemp1.Name = tblTemp2.Name
WHERE tblOne.Id = tblTemp2.Id

http://msdn.microsoft.com/en-us/library/aa260662%28SQL.80%29.aspx

Answer 2

UPDATE tblOne
SET
tblOne.Type = tblTemp1.Type
WHERE
tblOne.Id = tblTemp2.Id
AND
tblTemp1.Name = tblTemp2.Name;