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

Answer 2

I think you have to understand what are you doing.

Based on the mapping between Value in Table1 and Num in Table2 for property 55, you may do this by using a common table expression (CTE) to update Table1 with the values from Table2 for property 76.

WITH PropertyNames AS 
(
    SELECT t1.Object_id, t2.Name
    FROM Table1 t1
    JOIN Table2 t2 ON t1.Value = t2.NUM
    WHERE t1.Prop_id = 55 
) 
UPDATE t1 
SET t1.Value = pn.Name 
FROM Table1 t1 
JOIN PropertyNames pn ON t1.Object_id = pn.Object_id 
WHERE t1.Prop_id = 76;

Explanation: when Prop_id = 55, the CTE PropertyNames is constructed to translate Object_id to the relevant property name (Name) from Table 2.

Then, we update Table 1 with the relevant property names (Name) obtained from the CTE where Prop_id = 76.

Using the mapping between property 55 and the appropriate names in Table2, this query will update the Value in Table1 for property 76.