How to use the result of a stuff-function in an IN-Clause?

Question

I want to use the result of a concatenated string in an IN-Clause.

This is my SQL-Query:

UPDATE Customer
SET Agent = 'Test'
WHERE CAST(ID AS VARCHAR) IN (
        SELECT IIF((
                    SELECT WinnerID
                    FROM Customer
                    WHERE ID = '19560'
                    ) IS NULL, '19560', (
                    SELECT STUFF((
                                SELECT ',' + CAST(ID AS VARCHAR)
                                FROM Customer
                                WHERE WinnerID = '19560'
                                FOR XML PATH('')
                                ), 1, 1, '') --returns 19560, 19686
                    ))
        )

The part in the IIF functions where the expression returns false is the problem. The correct values are returned, but they are not recognized correctly, so the table is not updated.

This is the table structure with some sample date

ID	WinnerID	CustomerName	Agent
19560	19560	Carman Sansone	NULL
19686	19560	Melania Snowden	NULL
19404	NULL	Esther Flansburg	NULL
19405	NULL	Maximo Schill	NULL
20055	20055	Jeanice Moen	NULL
20056	20055	Kraig Rochin	NULL

The WinnerID is the result of a duplicate resolution. Thus, two customers who have the same WinnerID are the same customer and should be assigned the same agent.

Is there any way to formulate this part of the query so that I can use an IN-clause? Or to reformulate the whole query to achieve the desired outcome?

Answer 1

You're completely misunderstanding how IN works here. ID IN ('19560, 19686') would be equivilent to ID = '19560, 19686'. When using IN you need to return a data set, or provide a list. For example ID IN (19560, 19686).

There's no need to use FOR XML PATH (not STUFF, all STUFF does is removes and replaces a length of characters in a string with another string; in this case you are removing the first character, and replacing it with a zero length string) to create a delimited list, just use IN properly as shown in the documentation.

This is some what of a guess on the logic you want, as (with respect) it's a mess, but perhaps you just want this

UPDATE C
SET Agent = 'Test'
FROM Customer C
WHERE ID = 19560
   OR ID IN (SELECT sq.ID
             FROM Customer sq
             WHERE sq.WinnerID = 19560);