Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: SQL Subquery Error Help !!

    I have a problem with an SQL server query.
    The error that I am getting is the subquery returns more than one value.This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The following is my query :
    I have a temporary report table called ReportsAdditionalComment
    I am doing an update in this temp table and selecting additional comments.Now the problem is when the following statement runs inside the stored procedure it returns the above error of subquery returned more than one value.Obviously, I can make out since I am setting out into one variable , it will not accept if the inner query returns more than one row.But I don't know how to modify the SET part of this query so that it can return more than one Row.I need to have multiple comments returned and set in ADD_COMMENTS.

    Code:
    UPDATE #TEMP_ReportsAdditionalComment
    SET ADD_COMMENTS = (SELECT	RE.REMARKS   	
             		FROM	ReviewEvent	RE	
    			WHERE	RE.PROJECT_ID = @PROJ_ID
    			AND	RE.REPOINT_ID = @REPOINT_ID
    			AND	RE.REVIEW_DATE = @REVIEW_DATE
    			)
    The type of ADD_COMMENTS is VARCHAR(1000).
    I hope I have explained it clearly.I seemed to be stuck on this for the last two days and since I am a novice in SQL , I really don't know what could be the solution.By the way the inner SQL query executes perfectly and returns two rows but I don't know how to set up multiple rows in ADD_COMMENTS .I have also referred the forums but couldn't find a working solution for this problem. Please Help !!

  2. #2
    Join Date
    Jan 2010
    Posts
    2
    It would be fine with me if someone could just help me concatenate and set multiple rows returned from the subquery into The ADD_COMMENTS var using a delimiter or a comma in between.Please help

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    SET ADD_COMMENTS = (SELECT RE.REMARKS

    should be

    SET ADD_COMMENTS IN (SELECT RE.REMARKS
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PracticalProgram View Post
    SET ADD_COMMENTS = (SELECT RE.REMARKS

    should be

    SET ADD_COMMENTS IN (SELECT RE.REMARKS
    um, no it should not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You r right. Just looked at it again and noticed that it was an update with a subselect. First glanced and assumed it was a select with a subselect.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •