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.
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 );