update column values from another table with different column name but same column value in MS SQL

Question

update column values from another table with different column name but same column value

I have two tables as mentioned below :

Table1

ID | Name
1  | A
2  | A
3  | A
4  | A

Table2

IDX | Name
1   | XYZ
2   | PQR
3   | PPS

update Table1
set Name = (Select Name from Table2 where Table1.ID = Table2.IDX)

I'm getting below result after executing above query.

ID | Name
1  | XYZ
2  | PQR
3  | PPS
4  | NULL

But I need result as mentioned below:

ID | Name
1  | XYZ
2  | PQR
3  | PPS
4  | A

Can somebody help with this ? Thanks!

You could use isnull however a join is better as it only touches rows that can be updated. — Stu
– Stu, Commented Jun 11, 2022 at 7:32
Thank you below works for me code update Table1 set Name = (select Table2.name from Table2 where Table2.IDX = Table1.ID) where exists (select * from Table2 where Table1.ID = Table2.IDX);code — Yoyo Tech
– Yoyo Tech, Commented Jun 11, 2022 at 8:10

Tim Biegeleisen · Accepted Answer · 2022-06-11 09:00:59Z

2

Using an update join we can try:

UPDATE t1
SET Name = t2.Name
FROM Table1 t1
INNER JOIN Table2 t2
    ON t2.IDX = t1.ID;

Only records from Table1 which match to something in Table2 will be updated. This avoids the problem of making a null assignment from which your current approach suffers. You could make the following slight change to your current update to avoid the problem:

UPDATE Table1
SET Name = (SELECT COALESCE(t2.Name, Table1.Name) FROM Table2 t2
            WHERE Table1.ID = t2.IDX);

edited Jun 11, 2022 at 9:00

answered Jun 11, 2022 at 7:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yoyo Tech Over a year ago

getting syntax error : (UPDATE Table1 t1 SET Name = (SELECT COALESCE(t2.Name, t1.Name) FROM Table2 t2 WHERE t1.ID = t2.IDX);

Collectives™ on Stack Overflow

update column values from another table with different column name but same column value in MS SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related