SQL update records compared to records from another table

Should be fairly simple.

I have 2 tables. One of them has table1(ID,name,other_id) and the other t开发者_如何学Goable has table2(id,name,group,..)

I want table1.other_id to be same as table2.id based on the data in the name fields on both tables.

---

UPDATE t1
SET t1.other_id = t2.id
FROM Table1 t1
    JOIN Table2 t2 ON t1.name = t2.name

This is of course assuming you don't have multiple records with the same "name", otherwise how would you tell which record with a given name you want the ID for from table2.

---

This should do it:

update table1 t1
set other_id = (
    select id
    from table2 t2
    where t2.name = t1.name )

This will fail if table2 has multiple records with the same name.