Subquery returned more than 1 value in SQL SErver 2008

Question

Please see my previous question that I am still stuck with. Insert statement with sub queries

I have 4 columns I need to address and I cannot drop the table to change the 4th column from an int not null to a

I was directed FROM this original statement:

INSERT into ADVNET.dbo.KenCatItemTest
(categoryitemid,itemid,categoryid)
SELECT NEWID(),itemid,'0FCA508F-7EB5-4C2E-8803-DE688C4126E5'
FROM janel.dbo.item
WHERE janel.dbo.item.itemnumber like 'c-%' and listprice > 0

and it throws error:

Cannot insert the value NULL into column 'LineSequence', table 'ADVNET.dbo.KenCatItemTest'; column does not allow nulls. INSERT fails. The statement has been terminated.

So now I am looking at this statement instead:

DECLARE @CategoryItemId uniqueidentifier;  
SET @CategoryItemId = NEWID();  
DECLARE @ItemID uniqueidentifier;  
SET @ItemId = (select itemid from janel.dbo.item
WHERE janel.dbo.item.itemnumber like 'c-%' and listprice > 0 );
DECLARE @CategoryID uniqueidentifier;
SET @CategoryID = '0FCA508F-7EB5-4C2E-8803-DE688C4126E5';
DECLARE @LineSequence int;
SELECT @LineSequence = ISNULL(MAX(LineSequence),0) + 1  
FROM KenCatItemTest WHERE CategoryId = @CategoryId;
INSERT INTO ADVNET.dbo.KenCatItemTest
(CategoryItemId, ItemId, CategoryId, LineSequence)
VALUES (@CategoryItemId, @ItemId, @CategoryId, @LineSequence)

this new statement throws the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 515, Level 16, State 2, Line 5 Cannot insert the value NULL into column 'ItemId', table 'ADVNET.dbo.KenCatItemTest'; column does not allow nulls. INSERT fails. The statement has been terminated.

score 2 · Accepted Answer · answered Mar 05 '15 at 20:17

2

This statement is causing the problem:

SET @ItemId = (select itemid from janel.dbo.item
WHERE janel.dbo.item.itemnumber like 'c-%' and listprice > 0 );

If there is more than one row which satifies the condition then the error will occur because the SET statement expects there to be a scalar value returned on the right side and not a row set.

To fix it you can either narrow down the criteria to where only a single row is returned or do something like pull the first value.

For example:

SET @ItemId = (select TOP 1 itemid from janel.dbo.item
WHERE janel.dbo.item.itemnumber like 'c-%' and listprice > 0 );

answered Mar 05 '15 at 20:17

Jason Faulkner

6,378
2
28
33

thank you @Jason-Faulkner. but how can i accomplish the task of bringing all of the records over and creating all of the rows? Perhaps I should not be using scalar variables? – KenSummersNJ Mar 05 '15 at 20:21
1

@KEN - Easist way would be to use a `CURSOR`. https://msdn.microsoft.com/en-us/library/ms180169.aspx – Jason Faulkner Mar 05 '15 at 20:24
1

No need for a cursor to do inserts. I don't see anything in the code from the OP that would suggest any kind of looping is needed. – Sean Lange Mar 05 '15 at 20:28
1

@SeanLange - The `LineSequence` appears to have to be manually incremented for each row. Other than this, a `SELECT INTO` could be used. – Jason Faulkner Mar 05 '15 at 20:29
1

Sure incrementing numbers is painless using ROW_NUMBER or even a tally table. No need to resort to looping. – Sean Lange Mar 05 '15 at 20:50

score 1 · Answer 2 · answered Mar 06 '15 at 01:20

Can you not just use Row_number() to generate a value for LineSequence?

    INSERT INTO ADVNET.dbo.KenCatItemTest(
           categoryitemid
          ,itemid
          ,categoryid
          ,LineSequence)
    SELECT 
           NEWID()
          ,itemid
          ,'0FCA508F-7EB5-4C2E-8803-DE688C4126E5'
          ,ROW_NUMBER() OVER(ORDER BY ItemId)
      FROM 
           janel.dbo.item i
     WHERE 
           i.itemnumber LIKE 'c-%' 
       AND listprice > 0

Subquery returned more than 1 value in SQL SErver 2008

2 Answers2