在Insert语句中的多个Select语句中出现错误

I am writing a query which has multiple select statements in an insert statement

我正在编写一个在insert语句中包含多个select语句的查询

    INSERT INTO dbo.Products 
    (ProductName, 
     SupplierID, 
     CategoryID, 
     UnitsInStock, 
     UnitsOnOrder, 
     ReorderLevel, 
     Discontinued)
VALUES  
    ('Twinkies' , 
     (SELECT SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'),
     (SELECT CategoryID FROM dbo.Categories WHERE CategoryName = 'Confections'), 
     0, 
     0, 
     10, 
     0)

Actually it gives error

实际上它给错误

Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.

Where these two select statements returns only one value.

这两个select语句只返回一个值。

2 个解决方案

#1

Just change VALUES to SELECT and remove the outer parentheses.

只需更改值以选择并删除外部括号。

INSERT INTO dbo.Products 
(ProductName, 
 SupplierID, 
 CategoryID, 
 UnitsInStock, 
 UnitsOnOrder, 
 ReorderLevel, 
 Discontinued)
SELECT  
'Twinkies' , 
 (SELECT SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'),
 (SELECT CategoryID FROM dbo.Categories WHERE CategoryName = 'Confections'), 
 0, 
 0, 
 10, 
 0

You may also need a TOP 1 on the subexpressions, but that would give a different error message: subquery returned more than one value.

您可能还需要子表达式上的TOP 1，但是这会产生不同的错误消息:subquery返回多个值。

#2

Error message is correct, since

错误消息是正确的，因为

SELECT SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'

Can (technically) return multiple rows, which row should it display in the column?

能否(技术上)返回多个行，它应该在列中显示哪一行?

If you know that this subquery will only return one row, then tell the database to take the first one.

如果您知道这个子查询将只返回一行，那么请告诉数据库取第一行。

SELECT TOP 1 SupplierID FROM dbo.Suppliers WHERE CompanyName = 'Lyngbysild'

#1