Problem with updating the table via UPDATE SELECT

Question

I'm using MS SQL Server. I have 2 tables:

Table1 stores information about the individual object number (Object_id), the number of its property (Prop_id) and the value of this property (Value).

Example

Object_id	Prop_id	Value
1	55	980
1	76
2	55	970
2	76
3	55	960
3	76

Table 2 contains the values of the object properties:

NUM	Name
980	ABC
970	DEF
960	HIJ

I need to update table 1 with values from table 2. I need to put "Name" in the "Value" column for a property with index 76. The connection of the two tables should be by the fields "Value" for property 55 in Table 1 and "Num" in Table 2.

In the end, I want to get this:

Object_id	Prop_id	Value
1	55	980
1	76	ABC
2	55	970
2	76	DEF
3	55	960
3	76	HIJ

Please help me write a query that will give the desired result!

I have already tried several SQL queries, but they either do not update the table at all, or substitute only the first value from the data sample.

My attempt:

UPDATE Table1
SET Table1.Value = name1.name
FROM (SELECT Table2.name 
      FROM Table1 
      JOIN Table2 ON Table2.num = Table1.Value 
                  AND Table1.Prop_id = 55) AS name1
WHERE Table1.Prop_id = 76

This attempt updates the values in the table, but inserts only the first value from the SELECT query into all rows (i.e. the ABC value in all rows with property 76).

Answer 1

It is easier if you write the query step by step

First, you need to get the value from Table2 link to Table1

select *
from   Table1 t1
       inner join Table2 t2  on t1.Value = t2.NUM

Object_id	Prop_id	Value	NUM	Name
1	55	980	980	ABC
2	55	970	970	DEF
3	55	960	960	HIJ

You will get the Object_id with the associate Name value from Table2

Next, you join it back to Table1 for update

select *
from   Table1 t1
       inner join Table2 t2  on t1.Value = t2.NUM
       inner join Table1 t1u on t1.Object_id = t1u.Object_id
where  t1u.Prop_id = 76

Object_id	Prop_id	Value	NUM	Name	Object_id	Prop_id	Value
1	55	980	980	ABC	1	76	null
2	55	970	970	DEF	2	76	null
3	55	960	960	HIJ	3	76	null

Verify the result is what you want, just change the query to a update query

update t1u
set    Value = t2.Name
from   Table1 t1
       inner join Table2 t2  on t1.Value = t2.NUM
       inner join Table1 t1u on t1.Object_id = t1u.Object_id
where  t1u.Prop_id = 76

And you will get the result you want in Table1