Last post Nov 13, 2008 12:19 AM by massdriver
Nov 06, 2008 01:15 PM|mm8|LINK
Subqueries are not allowed in this context. Only scalar expressions are allowed.
What's wrong and how to fix??
Nov 06, 2008 02:12 PM|Nicolas Buduroi|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 02:14 PM|limno|LINK
You need a column name after the TOP (1):
SELECT TOP(1) yourColumnName FROM .....
Nov 06, 2008 02:18 PM|mm8|LINK
declare @var varchar(20)
set @var = (SELECT TOP(1)something ...WHERE Id = @something)
Nov 06, 2008 02:21 PM|limno|LINK
(Edit:SQL server 2008 only)
Nov 06, 2008 02:29 PM|Nicolas Buduroi|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 02:34 PM|limno|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 02:43 PM|Nicolas Buduroi|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 02:58 PM|limno|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 03:17 PM|Nicolas Buduroi|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.
Nov 06, 2008 03:42 PM|limno|LINK
Thanks for your input. You are right that your sample will not run with SQL Server 2000, even SQL Server 2005. I did test my simple query with my SQL Server 2008 instance and it works. Your sample can run too without problem. I need to search for more information
to make this more clear. I go ahead to edit my post with SQL server 2008 only.
My statement is within the context of scalar value in this question: "You cannot put two columns in your select top1 query." .
Nov 06, 2008 03:51 PM|Nicolas Buduroi|LINK
Ha, that's kool, I didn't know they changed this in SQL2K8, that's a very nice improvment, even though I pity the guys who wrote the parser for it ;-).
Have a nice day!
Nov 12, 2008 03:50 PM|Nicolas Buduroi|LINK
Hum... Who's choosing the right answer here?!? Whoever that is, that person really need to stop smoking whatever he's smoking because that must be not good for his healt. No wonder there's people with insanly high scores here.
Nov 12, 2008 04:10 PM|limno|LINK
People who marked thread are from MS online support. We as moderators do not regularly mark thread answer unless there is a request. If you feel something is not right for any threads, you can always to use the Report abuse button to report it to get someone's
attention. You can also post a question on the Feedback forum to address concern. How to mark an answer for a thread has been raised many times in that forum but you can still post your view on it. Here is the link to the Feedback forum:(http://forums.asp.net/AddPost.aspx?ForumID=188)
Nov 12, 2008 04:18 PM|Naom|LINK
IMO it would be really nice to have option to mark replies as helpful and as answers with different credit for Help and answer. But IMO again your replies were very helpful in this thread.
Nov 13, 2008 12:19 AM|massdriver|LINK
you can also do this:
FROM Foo WHERE ID = @something)
Order by z