插入语句中多选语句中的错误 [英] error in Multiple Select statements in Insert statement

问题描述

我正在编写一个在插入语句中有多个选择语句的查询

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

    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)

实际上它给出了错误

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 ','.

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

Where these two select statements returns only one value.

推荐答案

只需将 VALUES 更改为 SELECT 并删除外括号即可.

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

您可能还需要在子表达式上使用 TOP 1，但这会给出不同的错误消息:子查询返回了多个值.

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

这篇关于插入语句中多选语句中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！