Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Question Unanswered: UPDATE - Subquery returned more than 1 value

    HI - I'm trying to set staging_location equal to location_id from another table. However I'm getting the 'Subquery returned more than 1 value' error.

    Any suggestions on how to fix the code below?

    Code:
    update t_pick_detail
    set staging_location = (select hum.location_id
    		     from t_hu_master hum, t_pick_detail pkd
       		     where pkd.order_number = 'TEST0691880003' AND pkd.staged_quantity = 0)
    where exists
    (select hum.location_id
    from t_hu_master hum, t_pick_detail pkd
    where pkd.order_number = 'TEST0691880003' AND pkd.staged_quantity = 0);
    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's a simple way to debug your situation

    run the subquery by itself --

    select hum.location_id
    from t_hu_master hum, t_pick_detail pkd
    where pkd.order_number = 'TEST0691880003' AND pkd.staged_quantity = 0

    observe that it returns more than one row

    now figure out why it does this, and fix it so that it returns only one row


    helps?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    r937 -

    Good tip... I fixed it, so that it would only return one row, however when I run the update it updates staging location in all rows.

    Returns one row:
    Code:
    select hum.location_id
    from t_hu_master hum, t_pick_detail pkd
    where pkd.order_number = 'TEST0691880003'
    AND pkd.staged_quantity = 0 
    AND pkd.order_number = hum.control_number
    All rows affected: I don't know why?!?
    Code:
    update t_pick_detail
    set staging_location = (select hum.location_id
    		     from t_hu_master hum, t_pick_detail pkd
    		     where pkd.order_number = 'TEST0691880003' 
    		     AND pkd.staged_quantity = 0 
    		     AND pkd.order_number = hum.control_number)
    where exists
    (select hum.location_id
    from t_hu_master hum, t_pick_detail pkd
    where pkd.order_number = 'TEST0691880003' 
    AND pkd.staged_quantity = 0 
    AND pkd.order_number = hum.control_number);

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lbgto View Post
    All rows affected: I don't know why?!?
    that's easy

    the UPDATE is performed on all rows that satisfy the WHERE clause

    your WHERE clause has an EXISTS predicate, and as long as the EXISTS subquery returns at least one row, the WHERE clause will be true for all rows of the UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by lbgto View Post
    HI - I'm trying to set staging_location equal to location_id from another table. However I'm getting the 'Subquery returned more than 1 value' error.

    Any suggestions on how to fix the code below?

    Code:
    update t_pick_detail
    set staging_location = (select hum.location_id
    		     from t_hu_master hum, t_pick_detail pkd
       		     where pkd.order_number = 'TEST0691880003' AND pkd.staged_quantity = 0)
    where exists
    (select hum.location_id
    from t_hu_master hum, t_pick_detail pkd
    where pkd.order_number = 'TEST0691880003' AND pkd.staged_quantity = 0);
    Thanks!
    Is your background in DB2 z/OS?
    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.

  6. #6
    Join Date
    Oct 2009
    Posts
    24
    the UPDATE is performed on all rows that satisfy the WHERE clause

    your WHERE clause has an EXISTS predicate, and as long as the EXISTS subquery returns at least one row, the WHERE clause will be true for all rows of the UPDATE statement
    Makes sense.... that's not what I want to happen though, so I shouldn't use where exists. I just want to update one row.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you want is a joined update, for which the general form is:
    Code:
    UPDATE table1
       SET fldx = t2.fldy
      FROM table1 AS t1
    INNER
      JOIN table2 AS t2
        ON t1.keyfld = t2.keyfld
     WHERE t2.fldz = 'foo'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2009
    Posts
    24
    Fixed... works perfectly!!! Thanks!

    Code:
    update t_pick_detail
    set picked_quantity = planned_quantity, staged_quantity = planned_quantity, status = 'STAGED', staging_location = hum.location_id
    from t_pick_detail pkd
    inner
      join t_hu_master hum
      on pkd.order_number = hum.control_number
      where hum.control_number = 'TEST0691880003' AND pkd.staged_quantity = 0
    Last edited by lbgto; 01-14-10 at 17:10.

Posting Permissions

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