Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    9

    Question Unanswered: why can't the subquery of an insert return multiple rows?

    why can't the subquery of an insert return multiple rows? Appreciate any help.

    sqlserver 2005

    ERROR Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


    INSERT INTO [REQUEST_PRACTICE_PROCESS_STEP_STATUS]
    ([RQST_PRAC_ID]
    ,[RQST_STEP_STAT_ID]
    ,[CRE_DT]
    ,[LAST_CHG_DT]
    ,[LAST_CHG_USER_NM]
    ,[DATA_STAT_CD])
    SELECT
    (select rp2.RQST_PRAC_ID from REQUEST_PRACTICE rp2 WHERE rp2.prnt_id=rpss.RQST_PRAC_ID and rp2.DATA_STAT_CD='A' and rp2.prnt_id is not null)
    ,RQST_STEP_STAT_ID
    ,getdate()
    ,getdate()
    ,rpss.LAST_CHG_USER_NM
    ,rpss.DATA_STAT_CD
    FROM [REQUEST_PRACTICE_PROCESS_STEP_STATUS] rpss
    inner join REQUEST_PRACTICE rp on rpss.RQST_PRAC_ID=rp.RQST_PRAC_ID inner join ASSISTANCE_REQUEST ar on rp.ASST_RQST_ID=ar.asst_rqst_id where ar.asst_rqst_id=992;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because it is not allowed to, that's why not

    the problem is here --
    Code:
    INSERT 
      INTO ...
    SELECT ( select rp2.RQST_PRAC_ID 
               from REQUEST_PRACTICE rp2 
              WHERE rp2.prnt_id = rpss.RQST_PRAC_ID 
                and rp2.DATA_STAT_CD = 'A' 
                and rp2.prnt_id is not null )
         , ...
    apparently it is this subquery on rp2 that returns more than one row

    which is not allowed

    why don't you use a join instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    9

    Question

    Quote Originally Posted by r937
    because it is not allowed to, that's why not

    the problem is here --
    Code:
    INSERT 
      INTO ...
    SELECT ( select rp2.RQST_PRAC_ID 
               from REQUEST_PRACTICE rp2 
              WHERE rp2.prnt_id = rpss.RQST_PRAC_ID 
                and rp2.DATA_STAT_CD = 'A' 
                and rp2.prnt_id is not null )
         , ...
    apparently it is this subquery on rp2 that returns more than one row

    which is not allowed
    yes, it is obvious that is where the problem is - but my question was: Why is it not allowed?
    Could it be that if more than one row is returned, then the values of the fields to be populated from the outer select would be unknown?

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'll try to explain it with an example. When a "normal" SELECT would return a result of 1 record, you would get something like this:
    Code:
    A	1	2009-10-07	Arthur	Hello World
    Now suppose one of the values, the 'Name', would be a recordset of many CHAR(50)'s instead of just one CHAR(50)-value, you would get this :
    Code:
    A	1	2009-10-07	Arthur	Hello World
    				King
    				Of
    				The
    				Brittons
    You can not fit a set of multiple values into a container of type CHAR(50) for 1 value.

    Say you left shoe is a container for a value of type LeftFoot. You can get 1 left foot in it, not the set of all the left feet of the people of your country. Unless of coarse, if they were all a special selection of casualties of war and anti-personnel mines ... err, am I digressing?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2009
    Posts
    9
    Quote Originally Posted by Wim
    You can not fit a set of multiple values into a container of type CHAR(50) for 1 value.
    but INSERT can insert multiple rows....

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    INSERT INTO [REQUEST_PRACTICE_PROCESS_STEP_STATUS]
    ([RQST_PRAC_ID]
    ,[RQST_STEP_STAT_ID]
    ,[CRE_DT]
    ,[LAST_CHG_DT]
    ,[LAST_CHG_USER_NM]
    ,[DATA_STAT_CD])
        SELECT rp2.RQST_PRAC_ID 
    		 , RQST_STEP_STAT_ID
    		 , getdate()
    		 , getdate()
    		 , rpss.LAST_CHG_USER_NM
    		 , rpss.DATA_STAT_CD
          FROM [REQUEST_PRACTICE_PROCESS_STEP_STATUS] rpss
    INNER JOIN REQUEST_PRACTICE rp 
    		ON rpss.RQST_PRAC_ID=rp.RQST_PRAC_ID 
    INNER JOIN REQUEST_PRACTICE rp2
    		ON rpss.RQST_PRAC_ID=rp2.RQST_PRAC_ID  -- my bad 
    INNER JOIN ASSISTANCE_REQUEST ar 
    		ON rp.ASST_RQST_ID=ar.asst_rqst_id 
    	 WHERE ar.asst_rqst_id=992
    	   AND rp2.prnt_id=rpss.RQST_PRAC_ID 
    	   AND rp2.DATA_STAT_CD='A' 
    	   AND rp2.prnt_id IS NOT NULL
    ?????
    Last edited by Brett Kaiser; 10-07-09 at 16:39.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Yes, it can insert many rows with a single select, but as Brett illustrated, your SQL was trying to fit a set of values into a single row. For multiple rows to be generated you have to write your SQL to allow for multiple rows.

    Dave Nance

  8. #8
    Join Date
    Sep 2009
    Posts
    9

    Question

    Quote Originally Posted by Brett Kaiser
    Code:
    INNER JOIN REQUEST_PRACTICE rp 
    		ON rpss.RQST_PRAC_ID=rp.RQST_PRAC_ID 
    INNER JOIN REQUEST_PRACTICE rp2
    		ON rpss.RQST_PRAC_ID=rp.RQST_PRAC_ID
    Brett, thanks for replying. Was there a typo - should'nt the second join be

    INNER JOIN REQUEST_PRACTICE rp2
    ON rpss.RQST_PRAC_ID=rp2.RQST_PRAC_ID ?
    Last edited by anilp; 10-07-09 at 16:06.

Posting Permissions

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