SQL - Operator IN with operator WITH AS-CodePudding

Using postgres

The following SQL creates TestsTodo, having all the informations about a table tests. It also have a TestsTodoIds, having only the ids of TestsTodo

Then, i want to update all the rows of a table "test_results", with all the ids in TestsTodoIds. I can do it by setting "WHERE test_id IN (select id from TestsTodo)" But i cant do it with "WHERE test_id IN TestsTodoIds", which is basically the same, i don't understand why.

WITH 
    TestsTodo AS
    (
        -- Selecting from table tests
        
    ), 
    TestsTodoIds AS 
    ( 
        SELECT id FROM TestsTodo -- This returns all the. ids from TestsTodo
    )
    
    --UPDATE test_results 
    --SET status = 'FOUND_IN_DB' 
    --WHERE test_id IN (SELECT id FROM TestsTodo) 
    --RETURNING *  
    -- This works
    
    UPDATE test_results
    SET status = 'FOUND_IN_DB'
    WHERE test_id IN TestsTodoIds
    RETURNING * -- This does not

Error: ERROR: syntax error at or near "TestsTodoIds" LINE 31: WHERE test_id IN TestsTodoIds ^

CodePudding user response：

Your CTE called TestsTodoIds is a virtual table with one column. It isn't a set of values, and IN needs a set of values. You could use

WHERE test_id IN (SELECT id FROM TestsTodoIds)

and your query would function correctly.

That CTE is, in my opinion, unnecessary. The WHERE clause I suggested will perform exactly the same as

WHERE test_id IN (SELECT id FROM TestsTodo)

and you'll have less complexity to cope with when reading and reasoning about the query.