Fix for SQL UPDATE: updating all the rows to the same value instead of basing value on another table-CodePudding

I have the following query to combine the columns into a comma-separated list (using this because STRING_AGG does not work for me probably due to an older version of SQL Server which I do not have control over):

SELECT TOP 1 
    STUFF((SELECT ','   t1.HAZARD_code_desc 
           FROM HAZARD_codes t1 
           WHERE mask & 67584 <> 0 
           FOR XML PATH('')), 1, LEN(','), '')
FROM 
    HAZARD_codes t0, sheldus51_copy sc 
WHERE 
    t0.mask & 67584 <> 0

This is then used to update the value of column in another table using the following query:

UPDATE sheldus51_copy
SET hazard_type_combo = (SELECT TOP 1 
                             STUFF((SELECT ','   t1.HAZARD_code_desc 
                                    FROM HAZARD_codes t1 
                                    WHERE mask & sc.mask <> 0 
                                    FOR XML PATH('')), 1, LEN(','), '')
                         FROM HAZARD_codes t0, sheldus51_copy sc 
                         WHERE t0.mask & sc.mask <> 0)

However, the value of all the rows in the column hazard_type_combo are getting updated to the same value instead of being governed by the sc.mask value. i.e.

UPDATE table 
SET column = (result of an expression)

this updates all rows to the same value. What am I missing here?

Some data below:

Query:

select distinct(mask) 
from sheldus51_copy

Data:

Query:

select HAZARD_code_desc, mask 
from hazard_codes

Result:

Avalanche   1
Coastal 2
Drought 4
Earthquake  8
Flooding    16
Fog 32
Hail    64
Heat    128
Hurricane/Tropical Storm    256
Landslide   512
Lightning   1024
Severe Storm/Thunder Storm  2048
Tornado 4096
Tsunami/Seiche  8192
Volcano 16384
Wildfire    32768
Wind    65536
Winter Weather  131072

Example query

select HAZARD_code_desc, mask  
from hazard_codes 
where mask & 67584 <> 0

will match the rows where mask is 2048 and 65536 and therefore should set the value for hazard_type_combo to Severe Storm/Thunder Storm, Wind

CodePudding user response：

You need to reference and alias the sheldus51_copy table in a FROM clause at the top level, so that both the UPDATE and the nested select (correlated subquery) can reference the same rows. Try:

UPDATE sc
SET hazard_type_combo = STUFF((
            SELECT ','   h.HAZARD_code_desc 
            FROM HAZARD_codes h 
            WHERE h.mask & sc.mask <> 0
            -- ORDER BY ...
            FOR XML PATH('')
        ), 1, LEN(','), '')
FROM sheldus51_copy sc

Suggest also adding an ORDER BY h.HAZARD_code_desc or ORDER BY h.mask if you want a predictable result.

And if sc.mask can ever be zero (no codes), you can add an ISNULL() around the expression to assign a default such as 'None' or the empty string.