Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Philadelphia
    Posts
    8

    Angry Unanswered: Update multiple rows

    OK, I'm trying to update multiple rows in one table with multiple rows in anouther with a select:

    This is my query:

    UPDATE SA_LISTING
    SET SA_Parent =(
    SELECT
    pr.SA_ID
    FROM TCS_EMP_GRP_ASS a ,
    TCS_EMP_GRP_NODE g,
    TCS_EMP_GRP_ASS e,
    SA_LISTING s,
    SA_GROUPS_FOR_PERFORMANCE p,
    SA_LISTING pr
    WHERE a.STOP_NOM_DATE = 500000
    AND a.LDR = 'T'
    AND a.EMP_GRP_NODE_SK = g.EMP_GRP_NODE_SK
    AND e.EMP_GRP_NODE_SK = g.EMP_GRP_NODE_SK
    AND e.EMP_SK = s.TCS_ID
    AND g.EMP_GRP_NODE_SK = p.EMP_GRP_NODE_SK
    AND p.Leader_SA_ID = pr.SA_ID
    AND e.STOP_NOM_DATE = 500000
    AND s.TCS_ID <> pr.TCS_ID);


    but I'm getting a "single-row subquery returns more than one row"
    what is wrong withtthe query? How do I update multiple rows from a select?

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    It seems that you are missing a WHERE in your SET ... how do you know which record to update?

    UPDATE SA_LISTING
    SET SA_Parent =(SELECT
    pr.SA_ID
    ... WHERE a.STOP_NOM_DATE = 500000
    AND a.LDR = 'T'
    ... AND s.TCS_ID <> pr.TCS_ID)
    WHERE
    SA_Parent.SA_ID = ???

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311

    Re: Update multiple rows

    Originally posted by cavada1
    OK, I'm trying to update multiple rows in one table with multiple rows in anouther with a select:

    This is my query:

    UPDATE SA_LISTING
    SET SA_Parent =(
    SELECT
    pr.SA_ID
    FROM TCS_EMP_GRP_ASS a ,
    TCS_EMP_GRP_NODE g,
    TCS_EMP_GRP_ASS e,
    SA_LISTING s,
    SA_GROUPS_FOR_PERFORMANCE p,
    SA_LISTING pr
    WHERE a.STOP_NOM_DATE = 500000
    AND a.LDR = 'T'
    AND a.EMP_GRP_NODE_SK = g.EMP_GRP_NODE_SK
    AND e.EMP_GRP_NODE_SK = g.EMP_GRP_NODE_SK
    AND e.EMP_SK = s.TCS_ID
    AND g.EMP_GRP_NODE_SK = p.EMP_GRP_NODE_SK
    AND p.Leader_SA_ID = pr.SA_ID
    AND e.STOP_NOM_DATE = 500000
    AND s.TCS_ID <> pr.TCS_ID);


    but I'm getting a "single-row subquery returns more than one row"
    what is wrong withtthe query? How do I update multiple rows from a select?
    Hi,

    Basicly the error means that because of the = operator, Oracle expects the subquery to return one row. If you want to be able to retrieve more rows, use the IN operator. Otherwise, rewrite your subquery in a way that it will return only one row.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you are doing a correlated subquery you need to join the subquery table to the table your updating. Also do you want to update every single row in SA_LISTING or do you want to specify a where clause at the end. And dont forget if the subquery doesnt find a record it will set SA_PARENT to null.

    NOTE the join I specified is probably incorrect as I dont know your schema but you get the idea.

    Alan

    UPDATE SA_LISTING X -- NOTE THE ALIAS X
    SET SA_Parent =(
    SELECT
    pr.SA_ID
    FROM TCS_EMP_GRP_ASS a ,
    TCS_EMP_GRP_NODE g,
    TCS_EMP_GRP_ASS e,
    SA_LISTING s,
    SA_GROUPS_FOR_PERFORMANCE p,
    SA_LISTING pr
    WHERE a.STOP_NOM_DATE = 500000
    AND a.LDR = 'T'
    AND a.EMP_GRP_NODE_SK = g.EMP_GRP_NODE_SK
    AND e.EMP_GRP_NODE_SK = g.EMP_GRP_NODE_SK
    AND e.EMP_SK = s.TCS_ID
    AND g.EMP_GRP_NODE_SK = p.EMP_GRP_NODE_SK
    AND p.Leader_SA_ID = pr.SA_ID
    AND s.SA_ID = X.SA_ID -- NOTE THE JOIN TO THE UPDATING TABLE THOUGH PROBABLY WRONG
    AND e.STOP_NOM_DATE = 500000
    AND s.TCS_ID <> pr.TCS_ID);
    Last edited by AlanP; 10-03-03 at 07:08.

  5. #5
    Join Date
    Oct 2003
    Location
    Philadelphia
    Posts
    8

    Re: Update multiple rows

    Thanks for everyone's help. I actually just used a curosr to do the update. There aren't that many rows in the table, and it seems to work well.

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    one hint for user a cursor and a loop to to do the update. If the resultset of the cursor grwows over time, you might be facing performance problems, since a loop is expensive.

    Good luck
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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