Error Whwn Passing Multiple Rows

  • Hi all,

    I am getting error when I passed multiplt rows in less than condition:

    create table #t1

    ( ID int)

    INSERT INTO #t1

    SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8

    CREATE TABLE #t2

    (ID int)

    INSERT INTO #t2

    SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4

    SELECT ID FROM #t2

    WHERE ID < (SELECT ID FROM #t1)

    Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    How to pass multiple values in this condition?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (4/20/2015)


    Hi all,

    I am getting error when I passed multiplt rows in less than condition:

    create table #t1

    ( ID int)

    INSERT INTO #t1

    SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8

    CREATE TABLE #t2

    (ID int)

    INSERT INTO #t2

    SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4

    SELECT ID FROM #t2

    WHERE ID < (SELECT ID FROM #t1)

    Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    How to pass multiple values in this condition?

    Passing multiple values to a 'less than' condition does not make logical sense.

    Is this what you are trying to achieve? If not, what results are you trying to return?

    select ID

    from #t2

    where ID < (select max(ID)

    from #t1

    )

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • It depends what you need to do. If you're checking that the ID from #t2 is lesst than all the IDs in #t1, you can use the ALL keyword.

    SELECT ID FROM #t2

    WHERE ID < ALL (SELECT ID FROM #t1)

    What's the expected results based on the sample data you posted?

    -- Gianluca Sartori

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply