Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Nov 12, 2008 11:19 PM by massdriver
Nov 06, 2008 12:15 PM|LINK
Subqueries are not allowed in this context. Only scalar expressions are allowed.
What's wrong and how to fix??
Nov 06, 2008 01:12 PM|LINK
Hi, you hit one of the most annoying SQL limitation, the way to go around this is simple but tiring: you declare a variable, fill it with the value you want and the use that variable in your insert statement. Something like this:
DECLARE @foo int
SELECT TOP 1 @foo = ...
INSERT INTO Table1(x, y, z) VALUES (1, 2, @foo)
Nov 06, 2008 01:14 PM|LINK
You need a column name after the TOP (1):
SELECT TOP(1) yourColumnName FROM .....
Nov 06, 2008 01:18 PM|LINK
declare @var varchar(20)
set @var = (SELECT TOP(1)something ...WHERE Id = @something)
Nov 06, 2008 01:21 PM|LINK
(Edit:SQL server 2008 only)
Nov 06, 2008 01:29 PM|LINK
Sorry to contradict you limno but your code is wrong and will trigger the same error: "Subqueries are not allowed in this context. Only scalar expressions are allowed.". Maybe you meant:
INSERT INTO yourTable (col1, col2)
SELECT TOP 1 val1, valueForCol2 FROM someOthertable
Nov 06, 2008 01:34 PM|LINK
Your select query can return ONLY one value in this context. You cannot put two columns in your select top1 query.
You can try this:(Edit:SQL server 2008 only)
INSERT INTO yourTable (col1, col2) VALUES (val1, (SELECT TOP 1
valueForCol2 FROM someOthertable))
Nov 06, 2008 01:43 PM|LINK
Well "SELECT TOP 1 1, 2" works in my Query Analyzer (SQL2K) and i've tried your query and it thrown: "Subqueries are not allowed in this context. Only scalar expressions are allowed.". This is very basic SQL, have you really tried that code in an actual
Nov 06, 2008 01:58 PM|LINK
From what you posted, I don't think you were running the query I posted early. Of course, you can run the SELECT TOP 1 col1, col2 FROM yourTable query , but the original poster wants a TOP 1 scalar value in that place to make the INSERT work. My code sample
is for that purpose. Please check the query you are using. Or post it here again.
Nov 06, 2008 02:17 PM|LINK
First you said: "You cannot put two columns in your select top1 query." then: "Of course, you can run the SELECT TOP 1 col1, col2 FROM yourTable query", this is contradictory from my point of view. Secondly try this and tell me it doesn't raise any error:
CREATE TABLE #Foo (Bar int, Baz int)
INSERT INTO #Foo (Bar, Baz) VALUES (1, (SELECT TOP 1 2))
How is this code different from yours, it throw: "Subqueries are not allowed in this context. Only scalar expressions are allowed.". And if you don't believe me then I'll post a screenshot for you to see.