Subquery returns more than 1 value SQL error on multiple selection

Question

I wanted to have a temporary table that will insert values using select statements. But every time I ran the query, I always got error

Subquery returns more than 1 value. This is not permitted when the query follows =, !=, <, <=, >, >=, or when the subquery is used as an expression. The statement has been terminated. (0 row(s) affected)

It's weird cause it seems there's no errors in the code. But if there is, please correct me.

Here's the query:

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))

Insert into @tblKeywords (Keyword1, Keyword2, Keyword3)
Values(
(Select k from 
(Select Keyword k, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 1 and 3),

(Select kk from 
(Select Keyword kk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 4 and 6),

(Select kkk from 
(Select Keyword kkk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 7 and 9)
)

Select * from @tblKeywords

Have you fired Subqueries separately? If yes then what was rowcount for each subquery? — Romesh
– Romesh, Commented Jun 24, 2013 at 6:20
@Brenelyn : You have three sub-queries. One or more of these return more than one row. So arises the problem. Try running each subquery separately, you will find the problem. — Ravi Singh
– Ravi Singh, Commented Jun 24, 2013 at 6:24
It works fine when I ran the queries separately. But I want to get the results in just one query so I can put them in asp:gridview. — Brenelyn
– Brenelyn, Commented Jun 24, 2013 at 6:30
when you have between 1 and 3 for example that gives you three rows and not one. Do you think you need to add TOP 1? — Edper
– Edper, Commented Jun 24, 2013 at 6:32

Oleksandr Fedorenko · Accepted Answer · 2013-06-25 06:46:26Z

3

In SQLServer2005+ You can use option with common table expression

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))

;WITH cte AS
 (      
  SELECT Keyword, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum       
  FROM dbo.t_SMSKeyword  
  )
  INSERT @tblKeywords(Keyword1, Keyword2, Keyword3)
  SELECT c1.Keyword, c2.Keyword, c3.Keyword
  FROM cte c1 JOIN cte c2 ON c1.RowNum + 3 = c2.RowNum
              JOIN cte c3 ON c2.RowNum + 3 = c3.RowNum
  WHERE c1.RowNum BETWEEN 1 and 3

See example on SQLFiddle

Select 4 rows in the first column and 3 rows for the other columns

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))
;WITH cte AS
 (      
  SELECT Keyword, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum       
  FROM dbo.t_SMSKeyword  
  )
  INSERT @tblKeywords(Keyword1, Keyword2, Keyword3)
  SELECT c1.Keyword, c2.Keyword, c3.Keyword
  FROM cte c1 LEFT JOIN cte c2 ON c1.RowNum + 4 = c2.RowNum AND c2.RowNum < 8
              LEFT JOIN cte c3 ON c2.RowNum + 3 = c3.RowNum
  WHERE c1.RowNum BETWEEN 1 and 4

SELECT *
FROM @tblKeywords

Example for second solution SQLFiddle

edited Jun 25, 2013 at 6:46

answered Jun 24, 2013 at 8:42

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brenelyn Over a year ago

thanks! It works perfectly.. but may I ask if it's possible to select 4 rows in the first column then 3 rows for the other columns? Thank you again :)

chetan · Accepted Answer · 2013-06-24 06:35:46Z

You'll get your desired result with the following query

SQLFIDDLE

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))

Insert into @tblKeywords (Keyword1, Keyword2, Keyword3)
 select k,kk,kkk from 
(Select k, RowNum from 
(Select Keyword k, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 1 and 3) a,

(Select kk, RowNum from 
(Select Keyword kk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 4 and 6) b,

(Select kkk,RowNum from 
(Select Keyword kkk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 7 and 9) c

where a.RowNum = b.RowNum-3
and a.RowNum = c.RowNum-6;

Select * from @tblKeywords;

sangram parmar · Accepted Answer · 2013-06-24 08:00:08Z

try this

DECLARE @tblKeywords TABLE
    (
      Keyword1 VARCHAR(MAX) ,
      Keyword2 VARCHAR(MAX) ,
      Keyword3 VARCHAR(MAX)
    )

INSERT  INTO @tblKeywords
        ( Keyword1 ,
          Keyword2 ,
          Keyword3
        )
        SELECT  k ,
                ( SELECT    kk
                  FROM      ( SELECT    e.Keyword AS kk ,
                                        ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum1
                              FROM      t_SMSKeyword AS e
                            ) AS Emp1
                  WHERE     Emp1.RowNum1 = ( RowNum + 3 )
                ) ,
                ( SELECT    kkk
                  FROM      ( SELECT    e.Keyword AS kkk ,
                                        ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum1
                              FROM      t_SMSKeyword AS e
                            ) AS Emp1
                  WHERE     Emp1.RowNum1 = ( RowNum + 6 )
                )
        FROM    ( SELECT    e.Keyword AS k ,
                            ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum
                  FROM      t_SMSKeyword AS e
                ) AS mytable
        WHERE   mytable.RowNum BETWEEN 1 AND 3







SELECT  *
FROM    @tblKeywords

Collectives™ on Stack Overflow

Subquery returns more than 1 value SQL error on multiple selection

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related