replace values of table from values of another table where both tables don't have same no of rows

Question

I have two tables at the moment.

Table1
 +-----+----+
 id val
 1 a
 2 b
 3 c
 4 d
 5 e
 +-----+----+

Table2
 +----+-----+
 id val
 1 aa
 2 bb 
 3 cc
 +----+-----+

I want a resultant table to look like this replacing values in table1 from table2 like so and keeping the renaming intact

Resultant table
 +-----+-----+
 id val
 1 aa
 2 bb
 3 cc
 4 d
 5 e
 +-----+-----+

I've tried the following code

UPDATE Table1 
 SET Table1.val = Table2.val 
 where Table1.id = Table2.id

but i get an error that

table2.id cannot be bound

Is there any other solution ? Bear in mind that both tables do not have same length

Answer 1

The error is because you haven't included the Table2 in the FROM clause, in order to replace the value of val of Table1 by the value of val of Table2, you should include Table2 in the join.

update t1 set t1.val = t2.val from Table1 t1 
inner join Table2 t2 on t1.id=t2.id

Answer 2

The error you're receiving is because you haven't included the table in your FROM clause in your update statement.

UPDATE T1
SET T1.value = ISNULL(T2.value,T1.value)
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.id = T2.id

The above code resolves the problem - you could also remove the ISNULL and change the join to an INNER join as below:

UPDATE T1
SET T1.value = T2.value
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.id = T2.id

dbfiddle here

For further details see the docs on update

Answer 3

You can also use the MERGE statement. It's especially useful if Table2 is acting like a staging table from an external source and one day it's a superset of Table1 and then you want to add more rows.

MERGE Table1 AS T
USING Table2 AS S ON (T.id = S.id)
WHEN MATCHED THEN 
 UPDATE SET val = s.val;
GO