SQL子查询返回多个值- on执行错误

Question

我试图使用以下存储过程从另一个trade表中更新surveillance数据库中的trade表：

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

但在执行时，它会导致以下错误：

子查询返回的值超过一个。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，这是不允许的。

所以，我试着用我的程序的修改版本来解决这个问题：

CREATE PROCEDURE [dbo].[TradeData]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    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

执行时没有出现错误，但我在表中得到了错误的结果。有什么帮助吗？

Answer 1

希望这能给你正确的结果。

它将使您的脚本有效：

CREATE PROCEDURE [dbo].[TradeData]
AS
BEGIN
    UPDATE Trade
    SET SellerMarketMember = 
        (SELECT TOP 1 [SellerMember].[MemberCode]
         FROM [Member] As SellerMember 
         WHERE Trade.[SellerMarketMemberId] = [SellerMember].[MemberId])
    , SellerAccount = 
        (SELECT TOP 1 [SellerAccount].[AccountNumber]
         FROM [Account] As SellerAccount 
         WHERE Trade.[SellerAccountId] = [SellerAccount].[AccountId])
    , BuyerMarketMember = 
        (SELECT TOP 1 [BuyerMember].[MemberCode]
         FROM [Member] As BuyerMember 
         WHERE Trade.[BuyerMarketMemberId] = [BuyerMember].[MemberId])
    , BuyerAccount = 
        (SELECT TOP 1 [BuyerAccount].[AccountNumber]
         FROM [Account] As BuyerAccount 
         WHERE Trade.[BuyerAccountId] = [BuyerAccount].[AccountId])
         -- CancelTime looks strange. I can't tell what you are trying
    , CancelTime = 
      (SELECT CancelTime
       FROM Trade)
    FROM [surveillance].[dbo].Trade Trade
    WHERE [surveillance].[dbo].Trade.[Ticket] in (SELECT TicketNumber FROM Trade )

END

Answer 2

为了给出一个正确的答案，如果你能给出这些表格(贸易、会员等)的一些想法，会有帮助。是。在我看来，成员等等意味着要成为主表和交易表--您正在尝试用母版表中的详细信息更新事务表。如果是这样的话，我想出一点：

SELECT [SellerMember].[MemberCode]
                            FROM Trade
                            INNER JOIN [Member] As SellerMember ON Trade.[SellerMarketMemberId] = [SellerMember].[MemberId]

当然，Trade表将具有同一个卖方成员的多个记录，因此，这就是子查询返回多条记录的原因。

如果[SellerMember].[MemberCode]等确实意味着是唯一的值，正如我所预期的那样，select TOP 1 (或select distinct)应该给出正确的行为。