store query result, with multiple results as list variable to use later

Question

I want to put the results of a select statement into an @variable to use later on in the query. I dont know how many results there are going to be. I have tried;

SET @variable = SELECT column FROM table

RESULT

@variable=( 123213,321312,321321)

I want to then use the results as

UPDATE table SET column=1 WHERE column in @variable

Answer 1

Just use a temporary table:

SELECT column
INTO #tmp
FROM table;


UPDATE table
    SET column = 1
    WHERE column in (SELECT column FROM #tmp);

You can also use a table variable but that requires specifying the types of the columns to define the variable.

Answer 2

If you really want it to be in the form of a variable (available only at execution time), then you can declare a TABLE variable.

DECLARE @variable TABLE (Column1 INT);
INSERT INTO @variable
SELECT Column FROM Table

The better way would be to create a temporary table with SELECT INTO:

SELECT Column
INTO #variable
FROM table;

The you can use the @variable or #variable as a regular table in any query.

UPDATE table
SET Column = 1
WHERE column IN (SELECT COLUMN FROM @variable)

or

UPDATE table
SET Column = 1
WHERE column IN (SELECT COLUMN FROM #variable)

Although I would prefer / recommend the JOIN style:

UPDATE t
SET column = 1
FROM table t
    INNER JOIN #variable v on t.column = v.column

There are numerous reasons why I don't recommend using a table variable but one of the most important is for performance reasons.

The Query Optimizer will always generate an estimate of 1 for when reading data from a table variable, which will generate a less than optimal execution plan for the scenario in which @variable has a large number of rows.