修正SQL更新:将所有行更新为相同的值，而不是将值基于另一个表列。

Question

我有以下查询来将这些列组合成一个逗号分隔的列表(使用这个查询是因为STRING_AGG对我不起作用，可能是因为我无法控制的Server的旧版本)：

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

然后使用以下查询更新另一个表中列的值：

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)

但是，列hazard_type_combo中所有行的值将被更新为相同的值，而不是由sc.mask值管理。即

UPDATE table 
SET column = (result of an expression)

这会将所有行更新为相同的值。我在这里错过了什么？

以下是一些数据：

查询：

select distinct(mask) 
from sheldus51_copy

数据：

196608
131072
32
65536
131104
2048
64
256
1
4
1024
128
16
32768
67584
2
4096
512
8

查询：

select HAZARD_code_desc, mask 
from hazard_codes

结果：

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

示例查询

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

将匹配掩码为2048和65536的行，因此应该将hazard_type_combo的值设置为Severe Storm/Thunder Storm, Wind。

Answer 1

您需要在顶层的FROM子句中引用和别名sheldus51_copy表，这样UPDATE和嵌套select (关联子查询)都可以引用相同的行。尝试：

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

如果您想要一个可预测的结果，还建议按h.HAZARD_code_desc添加一个订单，或者按h.mask添加一个订单。

如果sc.mask可以是零(没有代码)，则可以在表达式周围添加一个ISNULL()，以分配默认值，如'None‘或空字符串。