1

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

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

1

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.