How to search all rows in a column for a value using STRING_SPLIT in an UPDATE Query

I have one table with a single number in each row and I need to determine if that number is in any row in another table that contains ; separated numbers.

I am currently using loops to go through the table searching the column for each row individually. As you can imagine, it is painfully slow.

I was hoping I could do something like: UPDATE [nbr_test] SET [found_flag] = 1 WHERE [nbr] IN ( SELECT [value] FROM STRING_SPLIT(( SELECT [numlist] FROM [findin] ),';') )

Surprisingly SSMS doesn't show any syntax errors, but I get the error "Subquery returned more than 1 value..." which makes sense, but is there a way to do something similar without using loops and taking forever? I am using SQL Server 2019

Here is an example:

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')

And this is ideally what I'd want:

|id|nbr  |found_flag|
|1 |12345|1         |
|2 |34567|0         |
|3 |56789|1         |

Solution

STRING_SPLIT() could only take an expression there (like a SELECT statement) if it produces a single result. As soon as there is more than one row in findin, this is no longer true. You can instead use CROSS APPLY to generate the IN() values:

UPDATE dbo.[nbr_test] 
  SET [found_flag] = 1 
  WHERE [nbr] IN 
  (
    SELECT s.[value] 
      FROM dbo.[findin] AS f
      CROSS APPLY 
      STRING_SPLIT(f.[numlist], ';') AS s
  );

Example db<>fiddle

Is it possible to also get the id from findin? Like SET found_flag=1, found_id=f.id ?

Sure, it gets a little more complicated. Here's one (possibly not the most efficient) way:

UPDATE n
   SET n.found_flag = 1,
       n.found_id   = sq.id
FROM dbo.[nbr_test] AS n
CROSS APPLY
  (
    SELECT f.id, s.[value] 
      FROM dbo.[findin] AS f
      CROSS APPLY 
      STRING_SPLIT(f.[numlist], ';') AS s
      WHERE s.[value] = n.nbr
  ) AS sq;

Updated db<>fiddle