A Subquery Generates An Error When It
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more subquery returned more than 1 value. this is not permitted when the subquery follows update about Stack Overflow the company Business Learn more about hiring developers or posting ads
Subquery Returned More Than 1 Value Update Statement
with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack subquery returned more than 1 row Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up “subquery returns more than 1 row” error. up vote 2 down vote
How To Fix Subquery Returned More Than 1 Value
favorite I am new to web programming and I'm trying to make a twitter-clone. At this point, I have 3 tables: users (id, name) id is the auto-generated id name of the user tweets (id, content, user_id) id is the auto-generated id content is the text of the tweet user_id is the id of the user that made the post followers (id, user_id, following_id) id is the auto-generated id subquery returned more than 1 value. insert into select user_id is the user who is doing the following following_id is the user that is being followed So, being new to sql as well, I am trying to build an SQL statement that would return the tweets that the currently-logged in user and of everyone he follows. I tried to use this statement, which works sometimes, but other times, I get an error that says "Subquery returns more than 1 row". Here is the statement: SELECT * FROM tweets WHERE user_id IN ((SELECT following_id FROM followers WHERE user_id = 1),1) ORDER BY date DESC I put 1 as an example here, which would be the id of the currently logged-in user. I haven't had any luck with this statement; any help would be greatly appreciated! Thank you. sql subquery share|improve this question edited Oct 5 '11 at 9:02 rahularyansharma 7,1471051100 asked Oct 5 '11 at 8:36 kurisukun 1,32942562 Since you mentioned you are new to web programming this might be helpfull: stackoverflow.com/questions/65512/… Its good practice not to use select * incase the table structure changes etc, prob wont affect your project at all but its a good habit to get into using columns rather than select * –Purplegoldfish Oct 5 '11 at 10:35
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or
Subquery Returned More Than 1 Value Concatenate
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Subquery Returned More Than 1 Value In Case Statement
Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only how to get more than one value from subquery takes a minute: Sign up MySQL Error: Subquery returns more than one row up vote 0 down vote favorite In my query I am fetching three columns (say A,B and Summary) Summary is based on B and A. I have http://stackoverflow.com/questions/7658650/subquery-returns-more-than-1-row-error to use a subquery to fetch Summary based on A and B. Problem: For one value of A (say A1) and one value of B (say B1), I get two values of Summary (say Summ1 and Summ2) in One column. Hence it gives Error: Subquery returns more than one row Instead I want to show it as below in my table: A1 B1 Summ1 A1 B1 Summ2 How can I do so? QUERY: SELECT a.FundIDRecv,a.SubscribeDt, b.FundName, ( SELECT c.PricePerWeek FROM tbl_Hive http://stackoverflow.com/questions/5324555/mysql-error-subquery-returns-more-than-one-row c WHERE c.FundID IN ( SELECT FundID from tbl_FundStatic WHERE FundID IN ( SELECT FundIDSend FROM tbl_FundSubscriptions WHERE FundIDRecv = a.FundIDRecv ) AND UserID = '14' ) ) as Price FROM tbl_FundSubscriptions a, tbl_Hive b WHERE a.FundIDRecv = b.FundID AND a.FundIDRecv IN ( SELECT FundIDRecv FROM tbl_FundSubscriptions WHERE FundIDSend IN ( SELECT FundID FROM tbl_FundStatic WHERE UserID = '14' ) ) Group by a.FundIDRecv SELECT statement for c.PricePerWeek generates that error mysql sql share|improve this question edited Feb 24 '14 at 17:39 Mr. Radical 1,7241922 asked Mar 16 '11 at 11:22 Parth Bhatt 13.1k23104200 1 show us your query that generates the error. –Nathan DeWitt Mar 16 '11 at 11:25 1 Post your query –Adam Lukaszczyk Mar 16 '11 at 11:25 1 Can you show your query? –Sachin Shanbhag Mar 16 '11 at 11:25 @Nathan,Dobiatowski,Sachin: I have posted my query. –Parth Bhatt Mar 16 '11 at 11:44 1 I think You will have a huge performance problems if tbl_Hive gets bigger. That's because You are using a subquery in a WHERE clause and event worse, it's nested. –Piotr Salaciak Mar 16 '11 at 12:05 | show 1 more comment 4 Answers 4 active oldest votes up vote 0 down vote accepted It sounds like you need to restructure it as a JOIN rather than a subquery. share|improve this answer answered Mar 16 '11 at 11:26 David Winant 70037 @David Winant: Thanks for yo
1Subquery returned more than 1 value. This is not permitted when the subquery follows http://www.sql-server-performance.com/2007/subquery-returned-more-than-1-value/ =, !=, <, <= , >, >= or when the subquery http://www.sqlservercentral.com/Forums/Topic1060650-392-1.aspx is used as an expression. Severity level:16. Description:This error message appears when you try to use subquery (correlated or not) that returns more than one value to the calling query. Consequences:The T-SQL statement can be parsed, but causes the error at runtime. Resolution:Error of the more than Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You must rewrite the subquery, so that it returns exactly one value. Versions:All versions of SQL Server. Example(s):SELECT o.* FROM Northwind.dbo.Orders oWHERE o.OrderDate = (SELECT OrderDate FROM Northwind.dbo.Orders) Remarks:In the subquery returned more above example returns the subquery the column OrderDate for all rows of the table Orders. This raises the error. One possible rewrite of the query might look like: SELECT o.* FROM Northwind.dbo.Orders oWHERE o.OrderDate = (SELECT MAX(OrderDate) FROM Northwind.dbo.Orders) Because of the aggregate function MAX() is guaranteed that exactly only value is returned and the subquery is syntactically valid. ASK A QUESTION Tweet Array Errors No comments yet... Be the first to leave a reply! Leave a Reply Click here to cancel reply. Popular Latest Tags Setting up Transactional Replication in SQL Server 2008 R2. November 30, -0001 Backing Up a SQL Server Database Directly Onto a Remote Server November 30, -0001 Recovering a SQL Server Database from Suspect Mode November 30, -0001 SQL Server T-SQL Tuning - NOT IN and NOT Exists November 30, -0001 SQL Server T-SQL Tuning - Temp Tables, Table Variables and Union November 30, -0001 Physical Join Operators in SQL Server - Hash Operator September 21, 2016 Physical
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » T-SQL (SS2K8) » Subquery Error Subquery Error Rate Topic Display Mode Topic Options Author Message BSavoieBSavoie Posted Tuesday, February 8, 2011 2:12 PM Old Hand Group: General Forum Members Last Login: Today @ 5:52 PM Points: 323, Visits: 785 I have a table with a DateTime2 column named EndDateTime. I'm trying to subtract 1 second from all of them. I get the error below. I don't understand the problem. I'm sure it's something simple, but I don't know what it is:update ProctorSlot set EndDateTime = DATEADD(second, -1, EndDateTime)generates this error:Msg 512, Level 16, State 1, Procedure ProctorSlot_PreventOverlap, Line 25Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. . Post #1060650 Larry KruseLarry Kruse Posted Tuesday, February 8, 2011 3:27 PM SSC Journeyman Group: General Forum Members Last Login: Thursday, August 25, 2016 3:16 PM Points: 97, Visits: 734 Gosh BSavoie, I tried it out and it all works okay for me. Here is my test case:create table #myTemp ( row_id INT IDENTITY( 1,1) NOT NULL, myTime DATETIME2 NOT NULL ) INSERT #myTemp ( myTime ) VALUES ( GETDATE())SELECT * FROM #myTempUPDATE #myTempSET myTime = DATEADD( second, -1, myTime )SELECT * FROM #myTempDROP TABLE #myTempIt almost seems to me like your error message is originating somewhere else. I didn't see any sub-queries in your code, and the DATEADD( ) function works with either DATETIME or DATETIME2 datatypes. Post #1060704 BSavoieBSavoie Posted Tuesday, February 8, 2011 3:33 PM Old Hand Group: General Forum Members Last Login: Today @ 5:52 PM Points: 323, Visits: 785 Well that's strange. But now that you mention it, I think this query worked in the past. . Post #1060711 BSavoieBSavoie Posted Tuesday, February 8, 2011 8:19 PM Old Hand Group: General Forum Members Last Login: Today @ 5:52 PM Points: 323, Visits: 785 There must be a conflict in the data, but I'm not seeing it. . Post #1060779 Mike JohnMike John Posted Wednesday, February 9, 2011 3:57 AM SSCrazy Group: General Forum Members Last Login: Monday, August 15, 2016 6:50 AM Points: 2,670, Visits: 5,953 I suspect that a trigger has been added to that table, and the trigger either includes a subquery or does not not allow for multiple rows being updated in some way. Post #1060968 BSavoieBSavoie Posted Wednesday, February 9, 2011 1:38 PM Old Hand Group: General Forum Members Last Login: Today @ 5