CODEWITHSUNDEEP
sqlsql-servert-sql
Ben
Ben

Reputation: 556

SQL Subquery returned more than 1 value

My query causes the following error:

Msg 512, Level 16, State 1, Procedure Item_insupd, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Query:

INSERT INTO [Total_Database].[dbo].[Item]
    (
        ItemID, 
        ItemNo,
        ItemDescription,
        Notes,
        StandardCost,
        SalesGLAccountID,
        ItemTypeID,
        Backorderable
    ) 
 (
    SELECT  [nr],
            [nr],
            [Latijn]+' '+[Subgroep]+' '+CAST([nr] as VARCHAR(255)),
            [NL]+' '+[Vorm]+' '+[Kenmerk]+' '+[Hoogte],[Inkoopprijs],
            (4),
            (case when [Productgroep]='PB' then 1 else 5 end),
            (1) 
    FROM    [ACCESDATA].[dbo].[Planten]
 );

I suspect this to happen because my subquery does not contain a WHERE, unfortunately I do not know how to construct a correct WHERE clause.

Upvotes: 3

Views: 10273

Answers (3)

chevhfghfghfgh
chevhfghfghfgh

Reputation: 137

INSERT INTO [Total_Database].[dbo].[Item]
    (
        ItemID, 
        ItemNo,
        ItemDescription,
        Notes,
        StandardCost,
        SalesGLAccountID,
        ItemTypeID,
        Backorderable
    ) IN
 (
    SELECT  [nr],
            [nr],
            [Latijn]+' '+[Subgroep]+' '+CAST([nr] as VARCHAR(255)),
            [NL]+' '+[Vorm]+' '+[Kenmerk]+' '+[Hoogte],[Inkoopprijs],
            (4),
            (case when [Productgroep]='PB' then 1 else 5 end),
            (1) 
    FROM    [ACCESDATA].[dbo].[Planten]
 );

Upvotes: -1

roman
roman

Reputation: 117606

I suspect the problem is in this string (line 26 in your code):

IF NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.SalesGLAccountID))
OR ((SELECT I.COGSGLAccountID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.COGSGLAccountID))

It looks like (SELECT I.COGSGLAccountID FROM Inserted I) return more than one row, so you're getting an error.

You're treating inserted as a one row table (for example, you're getting parameters from it like SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I, but inserted table can have more than one row. So in your case I think you have 3 options - check that there's only 1 row in inserted, rewrite trigger as set-based, or use cursor.

Here's sql fiddle with somewhat similar example.

Upvotes: 4

Charles Bretana
Charles Bretana

Reputation: 146603

If you really only want to insert one row, then You just add Where at the end, followed by some predicate (logical statement) that will be true for only one row in the table the query is reading from.

INSERT INTO [Total_Database].[dbo].[Item](ItemID,
    ItemNo,ItemDescription,Notes,StandardCost,SalesGLAccountID,
     ItemTypeID,Backorderable) 
SELECT  [nr],[nr],[Latijn]+' '+[Subgroep]+' '+CAST([nr] as VARCHAR(255)),
            [NL]+' '+[Vorm]+' '+[Kenmerk]+' '+[Hoogte],[Inkoopprijs],(4),
           (case when [Productgroep]='PB' then 1 else 5 end),(1) 
FROM  [ACCESDATA].[dbo].[Planten]
Where [SomeColumnName] = [some Value];

... but when performing an Insert using a select to generate the rows to be inserted, you just type the Select statement instead of the Values() clause, without surrounding parentheses. I think that because you had the surrounding parentheses, the query processor assumed you wanted that to be a single value. Do you want to only insert one row of data? or a whole set of data rows ?

Upvotes: 0

Related Questions