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

This script returns the subject error. Done this sort of thing before, been a while. Snow Blind

/*
Error Message
(16 row(s) affected)
Msg 512, Level 16, State 1, Line 59
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

(16 row(s) affected)
*/

 declare @Po as int
  set @Po = 99308

  declare @PoHistory table
      (
       [ID] [int] identity (1, 1)
      ,[Po] [int] null
      ,[Item#] [float] null
      ,[Qty] [float] null default (0.0)
      ,[RecQty] [float] null default (0.0)
      ,[Amount] [decimal] (10, 2) default (0)
      ,[AuthorizedAmount] [decimal] (10, 2) default (0)
      ,[PendingAmount] [decimal] (10, 2) default (0)
      ,[PaidAmount] [decimal] (10, 2) default (0)
      )
            
      insert into @PoHistory
            (
             [Po]
            ,[Item#]
            ,[Qty]
            ,[RecQty]
            ,[Amount]
            ,[AuthorizedAmount]
            
            )
            (
            select
                   [PO_ID]
                  ,[ITEM_]
                  ,[QTY]
                  ,[RECVDQTY]
                  ,[TOTCOST]
                  ,[AuthorizedAmount]
            from [TVENDPOD]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            )
            
      update @PoHistory set [PendingAmount] =
            (
            select
                  sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            group by [PO_ID], [ITEM_]
            )                  
      from @PoHistory, [ACCOUNTSPAYABLE_DETAIL]
      where [ACCOUNTSPAYABLE_DETAIL].[PO_ID] = [Po]
      and [ACCOUNTSPAYABLE_DETAIL].[ITEM_] = [Item#]
                   
      select
            *
      from @PoHistory
LVL 4
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please change update statement to below and try again.

update P
SET P.[PendingAmount] = x.TOTCOST
from @PoHistory P 
CROSS APPLY 
(
	SELECT sum([TOTCOST]) [TOTCOST]
	FROM [ACCOUNTSPAYABLE_DETAIL] H 
	WHERE H.[PO_ID] = P.[Po] and H.[ITEM_] = P.[Item]
	and [PO_ID] = @Po and [CHECKNUMBER] = 0 and [PAID_DATE] is null
)x

Open in new window

0
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerAuthor Commented:
Thank you Pawan Kumar, reply ASAP midnight CST here, time for a little rest. G
0
Pawan KumarDatabase ExpertCommented:
Complete and formatted code for you -

DECLARE @Po AS INT
SET @Po = 99308

DECLARE @PoHistory TABLE 
(
  [ID] [int] IDENTITY (1, 1),
  [Po] [int] NULL,
  [Item] [float] NULL,
  [Qty] [float] NULL DEFAULT (0.0),
  [RecQty] [float] NULL DEFAULT (0.0),
  [Amount] [decimal](10, 2) DEFAULT (0),
  [AuthorizedAmount] [decimal](10, 2) DEFAULT (0),
  [PendingAmount] [decimal](10, 2) DEFAULT (0),
  [PaidAmount] [decimal](10, 2) DEFAULT (0)
)

INSERT INTO @PoHistory ([Po],[Item],[Qty],[RecQty],[Amount],[AuthorizedAmount])  
SELECT [PO_ID],[ITEM_],[QTY],[RECVDQTY],[TOTCOST],[AuthorizedAmount] 
FROM [TVENDPOD]
	WHERE [RECORD_] IS NOT NULL AND [PO_ID] = @Po

UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
CROSS APPLY 
(
	SELECT
	  SUM([TOTCOST]) [TOTCOST]
	FROM [ACCOUNTSPAYABLE_DETAIL] H
	WHERE H.[PO_ID] = P.[Po] AND H.[ITEM_] = P.[Item]
	AND [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
) x

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase ExpertCommented:
Inner join can also be used ...another way..
BUT CROSS APPLY will always be helpful and fast in these kind of cases.

UDECLARE @Po AS INT
SET @Po = 99308

DECLARE @PoHistory TABLE 
(
  [ID] [int] IDENTITY (1, 1),
  [Po] [int] NULL,
  [Item] [float] NULL,
  [Qty] [float] NULL DEFAULT (0.0),
  [RecQty] [float] NULL DEFAULT (0.0),
  [Amount] [decimal](10, 2) DEFAULT (0),
  [AuthorizedAmount] [decimal](10, 2) DEFAULT (0),
  [PendingAmount] [decimal](10, 2) DEFAULT (0),
  [PaidAmount] [decimal](10, 2) DEFAULT (0)
)

INSERT INTO @PoHistory ([Po],[Item],[Qty],[RecQty],[Amount],[AuthorizedAmount])  
SELECT [PO_ID],[ITEM_],[QTY],[RECVDQTY],[TOTCOST],[AuthorizedAmount] 
FROM [TVENDPOD]
	WHERE [RECORD_] IS NOT NULL AND [PO_ID] = @Po

UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
INNER JOIN 
(
	SELECT
	  SUM([TOTCOST]) [TOTCOST] , H.[PO_ID],H.[ITEM_]
	FROM [ACCOUNTSPAYABLE_DETAIL] H
	WHERE [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
	GROUP BY H.[PO_ID],H.[ITEM_]
)x ON x.[PO_ID] = p.[Po] AND x.[ITEM_] = P.[Item]

select  * from @PoHistory

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Msg 512, Level 16, State 1, Line 59

your codes look ok...which line is line 59?
0
Pawan KumarDatabase ExpertCommented:
@Ryan -
The update statement is incorrect. The inner query is returning multiple records and we are assigning that to single. So effectively the author is updating single record with his statement but Multiple records needs to be updated.

@Greg -
Have you tried my suggestions? Thank you.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
The inner query is returning multiple records and we are assigning that to single.

it's returns a Sum, so it shouldn't return multiple records

select
                  sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            group by [PO_ID], [ITEM_]
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
wait, you probably right. I didn't see the group by:

select                   sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            group by [PO_ID], [ITEM_]
0
Pawan KumarDatabase ExpertCommented:
@Ryan -
SAMPLE FOR YOU --- MULTIPLE RECORDS

CREATE TABLE testSUM
(
	  [PO_ID] INT 
	, [ITEM_] INT 
	, [TOTCOST] INT 
)
GO

INSERT INTO testSUM VALUES ( 1,1,100 ) , ( 1,2,30 ) , ( 1,1,300 ) , (3,4,20)
GO

SELECT SUM([TOTCOST]) [TOTCOST] 
FROM testSUM
group by [PO_ID], [ITEM_]

/*------------------------
MULTIPLE RECORDS
------------------------*/
TOTCOST
-----------
400
30
20

(3 row(s) affected)

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this to handle inner query error:

update @PoHistory set [PendingAmount] =
            (
            select
                  sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            and [ITEM_] = a.[ITEM_]
            )
      from @PoHistory, [ACCOUNTSPAYABLE_DETAIL] a
      where a.[PO_ID] = [Po]
      and a.[ITEM_] = [Item#]

Open in new window

0
Pawan KumarDatabase ExpertCommented:
@Ryan - That will give you INCORRECT Result  !!

Need to set VALUE for ([Po],[Item#]) , your query will give same data for each combination of [Po],[Item#]).

Also note thats very old way of writing SQL code, you should not use that, We should use JOINs.

I have already gave 2 solutions. BOTH should work fine without any issues.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I think someone is getting aggressive here but it really doesn't matter.

i'm not sure what it mean by >> your query will give same data for each combination of [Po],[Item#]). but it's ok as it really doesn't matter now.

this is what i have and i got my stand.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNTSPAYABLE_DETAIL]') AND type in (N'U'))
DROP TABLE [dbo].[ACCOUNTSPAYABLE_DETAIL]
GO

CREATE TABLE [dbo].[ACCOUNTSPAYABLE_DETAIL](
	[PO_ID] [int] NULL,
	[CHECKNUMBER] [nchar](10) NULL,
	[PAID_DATE] [datetime] NULL,
	[RECORD_] [nchar](10) NULL,
	[ITEM_] [float] NULL,
	[TOTCOST] [money] NULL
) ON [PRIMARY]

INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 5, 15.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 322, 4.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 3, 43.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (4444, N'0         ', NULL, N'y         ', 5, 5.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (33, N'0         ', NULL, N'y         ', 3, 634.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 4, 5444.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 2, 423.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N't44       ', 3, 53.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'4         ', NULL, N'53        ', 2, 5.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'22        ', 5, 23.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'34        ', 443, 1.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'33        ', 443, 10.0000)


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TVENDPOD]') AND type in (N'U'))
DROP TABLE [dbo].[TVENDPOD]
GO

CREATE TABLE [dbo].[TVENDPOD](
	[PO_ID] [int] NULL,
	[ITEM_] [float] NULL,
	[QTY] [float] NULL,
	[RECVDQTY] [float] NULL,
	[TOTCOST] [money] NULL,
	[AuthorizedAmount] [money] NULL,
	[RECORD_] [nchar](10) NULL
) ON [PRIMARY]


INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 2, 34, 33, 4534.0000, 3.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 443, 45, 56, 434.0000, 53.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 43, 35, 4, 7.0000, 44.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (533422, 23, 4, 2, 53.0000, 23.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 433, 35, 75, 100.0000, 44.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 7, 4, 434, 44.0000, 54.0000, NULL)
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 5, 222, 43, 80.0000, 43.0000, N'3         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 54, 2, 24, 1.0000, 2.0000, N'43        ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 3, 4, 332, 2.0000, 3.0000, N'5         ')


declare @Po as int
  set @Po = 99308

 select   [PO_ID], [ITEM_], sum( [TOTCOST]) [TOTCOST]
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            group by [PO_ID], [ITEM_]
			order by [PO_ID], [ITEM_]
                
  declare @PoHistory table
      (
       [ID] [int] identity (1, 1)
      ,[Po] [int] null
      ,[Item#] [float] null
      ,[Qty] [float] null default (0.0)
      ,[RecQty] [float] null default (0.0)
      ,[Amount] [decimal] (10, 2) default (0)
      ,[AuthorizedAmount] [decimal] (10, 2) default (0)
      ,[PendingAmount] [decimal] (10, 2) default (0)
      ,[PaidAmount] [decimal] (10, 2) default (0)
      )
            
      insert into @PoHistory
            (
             [Po]
            ,[Item#]
            ,[Qty]
            ,[RecQty]
            ,[Amount]
            ,[AuthorizedAmount]
            
            )
            (
            select
                   [PO_ID]
                  ,[ITEM_]
                  ,[QTY]
                  ,[RECVDQTY]
                  ,[TOTCOST]
                  ,[AuthorizedAmount]
            from [TVENDPOD]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            )
  
     update @PoHistory set [PendingAmount] =
            (
            select
                  sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            and [ITEM_] = a.[ITEM_]
            )
      from @PoHistory, [ACCOUNTSPAYABLE_DETAIL] a
      where a.[PO_ID] = [Po]
      and a.[ITEM_] = [Item#]

      select
            *
      from @PoHistory 
     order by Po, [Item#]

Open in new window


Untitled.png
just try to solve it using existing method, rather than introduce new methods which asker may not familiar with.
0
Pawan KumarDatabase ExpertCommented:
Sorry missed the a.item in the inner query but it's inefficient to requery the same tables repeatedly. Simply query once and aggregate.  If you go with the repeat query and join method, just imagine if you had 10000 Item values. While that might be unrealistic for this particular sample problem,  it's important to think about data scaling.

--
UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
CROSS APPLY 
(
	SELECT
	  SUM([TOTCOST]) [TOTCOST]
	FROM [ACCOUNTSPAYABLE_DETAIL] H
	WHERE H.[PO_ID] = P.[Po] AND H.[ITEM_] = P.[Item#]
	AND [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
)x
--

Open in new window


Apart from that we are only hitting the tables once in the method the group by is also handled internally so that method will really scale well.

We are also using old code and at one point in time we have to upgrade and then this code will not work so you have to change lot of code at that time hence as per my suggestion we should use the latest code methodology.
0
Scott PletcherSenior DBACommented:
Simply:

update po
set [PendingAmount] = apd.TOTCOST
from @PoHistory po
inner join (
    select PO_ID, ITEM_, sum([TOTCOST]) AS TOTCOST
    from [ACCOUNTSPAYABLE_DETAIL]
    where [RECORD_] is not null
    and [PO_ID] = @Po
    and [CHECKNUMBER] = 0
    and [PAID_DATE] is null
    group by [PO_ID], [ITEM_]
) as apd on apd.PO_ID = po.Po and apd.ITEM_ = po.Item#
where po.Po = @Po
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
I had already given the same before , See this comment ( #a42396448 ) :)

https://www.experts-exchange.com/questions/29072685/Subquery-returned-more-than-1-value-This-is-not-permitted-when-the-subquery-follows-or-when-the-subquery-is-used-as-an-expression.html?anchor=a42397086¬ificationFollowed=201283630#a42396448

UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
INNER JOIN 
(
	SELECT
	  SUM([TOTCOST]) [TOTCOST] , H.[PO_ID],H.[ITEM_]
	FROM [ACCOUNTSPAYABLE_DETAIL] H
	WHERE [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL 
        AND [RECORD_] is not null
	GROUP BY H.[PO_ID],H.[ITEM_]
)x ON x.[PO_ID] = p.[Po] AND x.[ITEM_] = P.[Item]

Open in new window

0
Pawan KumarDatabase ExpertCommented:
@Author - you can choose from the two of my suggestion-


--

UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
CROSS APPLY 
(
	SELECT
	  SUM([TOTCOST]) [TOTCOST]
	FROM [ACCOUNTSPAYABLE_DETAIL] H
	WHERE H.[PO_ID] = P.[Po] AND H.[ITEM_] = P.[Item#]
	AND [RECORD_] is not null
	AND [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
)x

--

Open in new window


OR

--

UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
INNER JOIN 
(
	SELECT
	  SUM([TOTCOST]) [TOTCOST] , H.[PO_ID],H.[ITEM_]
	FROM [ACCOUNTSPAYABLE_DETAIL] H
	WHERE [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL 
    AND [RECORD_] is not null
	GROUP BY H.[PO_ID],H.[ITEM_]
)x ON x.[PO_ID] = p.[Po] AND x.[ITEM_] = P.[Item#]

--

Open in new window

0
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerAuthor Commented:
Thank you, everyone, for looking at this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.