asked
Subquery returned more than 1 value.
Hello, I'm trying to update a 'trade' table in 'surveillance' database from another 'trade' table in 'CSD_4_00' database using a stored procedure as follows: USE [CSD_4_00] GO CREATE PROCEDURE [dbo].[TradeData] AS BEGIN UPDATE [surveillance].[dbo].Trade SET SellerMarketMember = (SELECT [SellerMember].[MemberCode] FROM Trade INNER JOIN [Member] As SellerMember ON Trade.[SellerMarketMemberId] = [SellerMember].[MemberId]) , SellerAccount = (SELECT [SellerAccount].[AccountNumber] FROM Trade INNER JOIN [Account] As SellerAccount ON Trade.[SellerAccountId] = [SellerAccount].[AccountId]) , BuyerMarketMember = (SELECT [BuyerMember].[MemberCode] FROM Trade INNER JOIN [Member] As BuyerMember ON Trade.[BuyerMarketMemberId] = [BuyerMember].[MemberId]) , BuyerAccount = (SELECT [BuyerAccount].[AccountNumber] FROM Trade INNER JOIN [Account] As BuyerAccount ON Trade.[BuyerAccountId] = [BuyerAccount].[AccountId]) , CancelTime = (SELECT CancelTime FROM Trade) WHERE [surveillance].[dbo].Trade.[Ticket] = (SELECT TicketNumber FROM Trade ) END GO but on execution it resulted in the following error: 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' So, I tried to get round the problem using this modified version of my procedure: CREATE PROCEDURE [dbo].[TradeData] AS BEGIN UPDATE [surveillance].[dbo].Trade SET SellerMarketMember = [SellerMember].[MemberCode] FROM Trade as T INNER JOIN [Member] As SellerMember ON T.[SellerMarketMemberId] = [SellerMember].[MemberId] WHERE [surveillance].[dbo].Trade.[Ticket] in(SELECT TicketNumber FROM Trade ) UPDATE [surveillance].[dbo].Trade set SellerAccount = [SellerAccount].[AccountNumber] FROM Trade as T INNER JOIN [Account] As SellerAccount ON T.[SellerAccountId] = [SellerAccount].[AccountId] WHERE [surveillance].[dbo].Trade.[Ticket] in(SELECT TicketNumber FROM Trade ) UPDATE [surveillance].[dbo].Trade set BuyerMarketMember = [BuyerMember].[MemberCode] FROM Trade as T INNER JOIN [Member] As BuyerMember ON T.[BuyerMarketMemberId] = [BuyerMember].[MemberId] WHERE [surveillance].[dbo].Trade.[Ticket] in(SELECT TicketNumber FROM Trade ) UPDATE [surveillance].[dbo].Trade set BuyerAccount = [BuyerAccount].[AccountNumber] FROM Trade as T INNER JOIN [Account] As BuyerAccount ON T.[BuyerAccountId] = [BuyerAccount].[AccountId] WHERE [surveillance].[dbo].Trade.[Ticket] in(SELECT TicketNumber FROM Trade ) UPDATE [surveillance].[dbo].Trade set CancelTime = T.CancelTime FROM Trade as T WHERE [surveillance].[dbo].Trade.[Ticket] in(SELECT TicketNumber FROM Trade ) END GO No errors appeared on execution, but I got wrong results in my table. any help? Thanks,
I'd start with a simple select that shows the data that is going to be updated. (I'm assuming here that records exists in all these tables, otherwise you will have to handle that with outer joins) select SurveillanceTrade.TicketNumber, Trade.TicketNumber, [SellerMember].[MemberCode], [SellerAccount].[AccountNumber], [BuyerMember].[MemberCode], [BuyerAccount].[AccountNumber], Trade.CancelTime from [surveillance].[dbo].Trade as SurveillanceTrade inner join CSD_4_00.dbo.Trade as Trade on Trade.TicketNumber = SurveillanceTrade.TicketNumber INNER JOIN [Member] As SellerMember ON Trade.[SellerMarketMemberId] = [SellerMember].[MemberId] INNER JOIN [Account] As SellerAccount ON Trade.[SellerAccountId] = [SellerAccount].[AccountId] INNER JOIN [Member] As BuyerMember ON Trade.[BuyerMarketMemberId] = [BuyerMember].[MemberId] INNER JOIN [Account] As BuyerAccount ON Trade.[BuyerAccountId] = [BuyerAccount].[AccountId] and if that all looks good, turn that into an update statement update SurveillanceTrade set SellerMarketMember = [SellerMember].[MemberCode], SellerAccount = [SellerAccount].[AccountNumber], BuyerMarketMember = [BuyerMember].[MemberCode], BuyerAccount = [BuyerAccount].[AccountNumber], CancelTime = Trade.CancelTime from [surveillance].[dbo].Trade as SurveillanceTrade inner join CSD_4_00.dbo.Trade as Trade on Trade.TicketNumber = SurveillanceTrade.TicketNumber INNER JOIN [Member] As SellerMember ON Trade.[SellerMarketMemberId] = [SellerMember].[MemberId] INNER JOIN [Account] As SellerAccount ON Trade.[SellerAccountId] = [SellerAccount].[AccountId] INNER JOIN [Member] As BuyerMember ON Trade.[BuyerMarketMemberId] = [BuyerMember].[MemberId] INNER JOIN [Account] As BuyerAccount ON Trade.[BuyerAccountId] = [BuyerAccount].[AccountId]
14 People are following this question.
