CODEWITHSUNDEEP
sqlsql-update
reyur
reyur

Reputation: 1

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 table 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.

Upvotes: 0

Views: 3331

Answers (2)

Aaron
Aaron

Reputation: 7098

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.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147334

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.

Upvotes: 2

Related Questions