sql - 子查询返回超过 1 个值 如何修复
问题描述
我面临一个问题。我有两张桌子FreshStock
和PurchaseInvoiceDetails
.
当收到新产品时,它会进入PurchaseInvoiceDetails
并且我希望它也进入第二张桌子。
我有代码首先检查产品是否未添加到 中FreshStock
,然后它首先添加它,但如果它已经添加了它,那么它会通过 ProductName 更新它。更新时出现错误:
消息 512,级别 16,状态 1,第 13 行
子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时,这是不允许的。该语句已终止。
我的代码:
IF NOT EXISTS(SELECT * FROM [dbo].[FreshStock] WHERE [ProductName] = 'Makki Rusk')
BEGIN
INSERT INTO [dbo].[FreshStock] ([ProductCode], [ProductName], [Stock],[IsAvailable])
SELECT [ProductCode], [ProductName], SUM([Box]), 1
FROM [dbo].[PurchaseInvoiceDetails]
WHERE ProductName = 'Makki Rusk'
GROUP BY [ProductCode],[ProductName]
RETURN
END
IF EXISTS(SELECT * FROM [dbo].[FreshStock] WHERE [ProductName] = 'Makki Rusk')
BEGIN
UPDATE [dbo].[FreshStock]
SET [ProductCode] = (SELECT [ProductCode]
FROM [dbo].[PurchaseInvoiceDetails]
WHERE [ProductName] = 'Makki Rusk'),
[ProductName] = (SELECT [ProductName]
FROM [dbo].[PurchaseInvoiceDetails]
WHERE [ProductName] = 'Makki Rusk'),
[Stock] = (SELECT SUM([Box])
FROM [dbo].[PurchaseInvoiceDetails]
WHERE [ProductName] = 'Makki Rusk')
WHERE ProductName = 'Makki Rusk'
END
解决方案
您收到的错误表明以下子查询之一正在返回多行,需要修改为仅返回单行:
[ProductName] = SELECT [ProductCode] FROM [dbo].[PurchaseInvoiceDetails] WHERE [ProductName] = 'Makki Rusk'
[ProductName] = (SELECT [ProductName] FROM [dbo].[PurchaseInvoiceDetails] WHERE [ProductName] = 'Makki Rusk')