如何在UPDATE查询中使用STRING_SPLIT搜索列中所有行的指定值？

问题 #

现有两张表：

• nbr_test：每行存储单个数字，包含id、nbr、found_flag字段
• findin：某列numlist存储以分号分隔的数字集合

需要判断nbr_test中的数字是否存在于findin任意行的数字集合中，目前用循环逐行查询速度极慢。尝试以下语句后：

UPDATE [nbr_test] SET [found_flag] = 1 WHERE [nbr] IN ( SELECT [value] FROM STRING_SPLIT(( SELECT [numlist] FROM [findin] ),';') )

SSMS无语法报错，但提示「子查询返回多个值」。求SQL Server 2019中无需循环的高效实现方式。

测试数据：

CREATE TABLE [nbr_test] (
    [id]    int identity(1,1),
    [nbr]   varchar(50),
    [found_flag] bit
)

CREATE TABLE [findin] (
    [id]    int identity(1,1),
    [numlist] varchar(255)
)

INSERT INTO [nbr_test] VALUES
('12345',0),
('34567',0),
('56789',0)

INSERT INTO [findin] VALUES
('23456;67890'),
('98765;12345;01234'),
('06789'),
('56789')

期望结果：
|id|nbr |found_flag|
|1 |12345|1 |
|2 |34567|0 |
|3 |56789|1 |

解决方案 #

错误原因 #

原语句报错是因为(SELECT [numlist] FROM [findin])会返回多行结果，但STRING_SPLIT仅能接收单个字符串参数，无法处理多行输入，因此触发「子查询返回多个值」的错误。

高效实现代码 #

使用CROSS APPLY配合STRING_SPLIT，拆分findin每一行的numlist后关联更新：

UPDATE nt
SET nt.found_flag = 1
FROM nbr_test nt
JOIN (
    SELECT TRIM(s.value) AS split_num
    FROM findin f
    CROSS APPLY STRING_SPLIT(f.numlist, ';') s
) fn ON nt.nbr = fn.split_num
WHERE nt.found_flag = 0; -- 仅更新未标记行，提升效率

关键说明 #

1. CROSS APPLY会对findin的每一行执行STRING_SPLIT，将分号分隔的字符串拆分成多行单个数字
2. TRIM用于处理拆分后可能存在的空格（数据无空格可省略）
3. 通过JOIN直接关联匹配的数字，批量更新found_flag，避免循环的性能损耗
4. 添加WHERE nt.found_flag = 0避免重复更新已标记的行，进一步优化性能

若需提前验证匹配结果，可执行查询：

SELECT nt.id, nt.nbr, CASE WHEN fn.split_num IS NOT NULL THEN 1 ELSE 0 END AS found_flag
FROM nbr_test nt
LEFT JOIN (
    SELECT TRIM(s.value) AS split_num
    FROM findin f
    CROSS APPLY STRING_SPLIT(f.numlist, ';') s
) fn ON nt.nbr = fn.split_num;

内容的提问来源于stack exchange，提问作者Dizzy49