Update 1 table from another 1

[newmediaguy]

Hi Guys,

I have had a quick search for a similar topic as I am sure it would have been asked before.......

Right, what i need to do is to update a table with the record ID of another. They both have a matching field.

tbl 1
RecID
Name

tbl 2
UID
Name
Category

I need to update tbl 2 with the RecID where tbl1.name = tbl2.name

I try in the SQL window in SQL 2000 and i get an error stating that the select statment is not supported in that query type.

I can do it in ASP but really would like to develop my SQL skills.

If you want any more info then let me know.

Any advice or help would be appreciated.

Many thanks

Glen

 

[rsinj]

update tbl2
SET UID = RecID
FROM tbl1
WHERE tbl2.Name = tbl1.Name

or

update tbl2
set UID = (Select RecId
from tbl1
where tbl2.Name = tbl1.Name)

 

[newmediaguy]

Thank you VERY much that has just saved about 3 hours of data entry!!

Much appreciated

Many thanks

Glen