Join and update same column sql server

Question

I have 2 tables as :

TableA:

ID Value
1  A
2  B

TableB:

   Rank Value
    12   A
    13   B

I need to join both tables on Value column and update Value column of TableB with TableA's ID column. So result TableB will be:

Rank Value
12   1
13   2

I am using Sql Server 2008

Answer 1

Use update with join:

update b
set b.value = a.id
from tableb b 
  join tablea a on b.value = a.value

• SQL Fiddle Demo

Answer 2

In SQL Server, you can do this with a join in the update. The specific syntax in your case is:

update b
    set value = a.id
    from tableb b join
         tablea a
         on b.value = a.value;