Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: Update Table With Values From Another Table

    HI ALL,
    MY DATABASE IS RUNNING ORACLE 10g.
    I WANT TO UPDATE A TABLE SAY table 1 WITH VALUES FROM THE COLUMN OF ANOTHER TABLE SAY table 2. PLEASE CAN SOMEONE POINT OUT THE RIGHT WAY OF DOING IT SINCE I HAVE TO UPDATE MULTIPLE ROWS.
    I TRIED THIS SQL

    UPDATE TABLEA
    SET b = (SELECT b1 from TABLEB WHERE TABLEB.a1 = TABLEA.a )
    WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a)
    AND THE QUERY RETURNED ERROR ORA-01427 "single-row subquery returns more than one row"


    AGAIN I RAN THIS OTHER QUERY
    UPDATE TABLEA
    SET b = (SELECT b1 from TABLEB WHERE TABLEB.a1 = TABLEA.a )
    AND THESAME ERROR RETURNED.
    WHAT DO I DO?

    THANKS
    SOGA

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORA-01427 "single-row subquery returns more than one row"
    Do you understand why you are getting this error? Apparently not.

    >SET b = (SELECT b1 from TABLEB WHERE TABLEB.a1 = TABLEA.a )
    The SELECT is returning more than 1 row.
    Oracle reports an error because it does not know which of the returned values should be assigned to "b".

    The way to avoid this error is to ensure that only a single row is returned by the SELECT statement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    correlated sql update

    UPDATE with a Subquery.

    Suppose, however, that you do not want to set the salaries to a single
    constant but you would rather copy the appropriate values from table EMP.
    To do this, you introduce two subqueries into the UPDATE statement; one
    in the SET clause and one in the WHERE clause:


    - Qualified update:

    UPDATE BONUS
    SET SAL =
    (SELECT EMP.SAL /* Subquery 1 (SET) */
    FROM EMP
    WHERE EMP.ENAME = BONUS.ENAME)
    WHERE EXISTS
    (SELECT * /* Subquery 2 (WHERE) */
    FROM EMP
    WHERE EMP.ENAME = BONUS.ENAME)
    [Statement 3].

    For easier reference, refer to Subquery 2 as the WHERE subquery
    because it is part of the UPDATE statement's WHERE clause, and
    Subquery 1 shall be called the SET subquery.


    - Explanation of key parts of the query:

    As in Statement 2, the WHERE clause of the UPDATE statement restricts
    the rows that are considered for updating. For each row in BONUS, the
    WHERE clause is evaluated for its truth value based on the values in
    that particular row of the table.

    An EXISTS condition is considered TRUE if the subquery following the
    keyword returns at least one row. The data in columns of the SELECT
    list is not significant, but whether the subquery returns any rows is
    significant. You 'SELECT *' since it does not matter which columns you
    name. In this case, the WHERE subquery returns rows only if the ENAME
    column in the BONUS table has a corresponding value for that column for
    some row in the EMP table (EMP.ENAME = BONUS.ENAME). This subquery
    ensures that you only update those rows in the BONUS table which have
    counterparts in EMP.


    - How the query works:

    While Statement 2 specifies that SAL be set to a constant, in Statement 3
    you SET SAL to the result of the SET subquery, a correlated subquery that
    retrieves the desired value from EMP. The SET subquery is very similar to
    the WHERE subquery. In fact, the only difference is the SELECT list.
    While the SELECT list is unimportant in the WHERE subquery, in the SET
    subquery it identifies the value which you want to assign to the SAL
    column for a particular row in the BONUS table. Since you want to assign
    the value of SAL in EMP to the value of SAL in BONUS, you SELECT EMP.SAL
    FROM EMP. As in the WHERE subquery, the WHERE clause of the SET subquery
    matches the row in BONUS being updated to its corresponding row in EMP.

    Observe that the SET subquery is executed once for each row in BONUS
    that satisfies the WHERE clause of the UPDATE statement, and substitutes
    values from the row being updated for columns named in the subquery.
    For example, the row in BONUS where ENAME='FORD' satisfies the WHERE
    clause of the UPDATE, since there also exists a row in EMP where ENAME
    is FORD. Thus, the database executes the SET subquery, substituting the
    column name BONUS.ENAME with the value 'FORD'.

    SELECT EMP.SAL
    FROM EMP
    WHERE EMP.ENAME = 'FORD'
    [Statement 4].

    This substitution of values in the subquery constitutes the "correlation"
    between the subquery and the UPDATE statement that contains it. The result
    returned by this subquery is a single row that contains 3000.00 for the
    EMP.ENAME column. This value is assigned to the SAL column in BONUS for
    the row where ENAME equals FORD.


    - What if not all rows in BONUS have a corresponding record in EMP?

    It may be that only some rows in BONUS satisfy the WHERE clause of the
    UPDATE statement. For example, when BONUS.ENAME='ROSCOE' there is
    no corresponding row in the EMP table for which EMP.ENAME ='ROSCOE'.
    Thus, when processing the UPDATE, the database skips over this row and
    the SET subquery is not executed.

    Check Appendix A for details on how to load ROSCOE into your tables.


    o Rule of Correlated UPDATEs.

    The different functions of the WHERE clause of the SET subquery
    and the WHERE clause of the UPDATE in Statement 3 are now clear:

    1 - The WHERE clause of the UPDATE statement determines which
    rows in the table are going to be updated.
    2 - The WHERE clause of the correlated subquery on the right side of
    a SET clause matches a row of the table being updated with the
    corresponding row from the tables named in the subquery.

    Point 2 above is an extremely important fact that follows directly from
    the rules specified below:


    - Prime Rule of Correlated UPDATEs:

    The correlated subquery on the right side of the SET clause MUST
    return EXACTLY one row for each row that is being updated.


    - The root cause of most UPDATE errors:

    * The right side of SETs where the clause returns > 1 row.

    Almost all errors with UPDATE statements can be traced to violations
    of this rule. Why is this rule important? Suppose that the SET
    subquery returns five rows for a row you are updating. Which row
    should the database choose in order to assign values to the columns
    listed on the left side of the SET clause? The first row? The third
    row? The value in the database is undefined and the database does not
    choose a value at random, hence the error occurs. In this case it is:

    ORA-1427: "single-row subquery returns more than one row".

    * The left side of SETs where the clause returns > 1 row.

    Consider the opposite case, in which the SET subquery returns no rows.
    Database versions before 6.0 could not assign a value to the left side
    of the SET clause if the subquery returned no rows, which again causes
    an error. This time the error is:

    ORA-1426: "single-row subquery returns no rows".

  4. #4
    Join Date
    Dec 2007
    Location
    India
    Posts
    5

    May I help You

    Hi ,
    In the Where clause you have given Select 1 instead of Select b1 . Remove the error and run the query again .
    Regards ,
    Experts


    Quote Originally Posted by Olusoga
    HI ALL,
    MY DATABASE IS RUNNING ORACLE 10g.
    I WANT TO UPDATE A TABLE SAY table 1 WITH VALUES FROM THE COLUMN OF ANOTHER TABLE SAY table 2. PLEASE CAN SOMEONE POINT OUT THE RIGHT WAY OF DOING IT SINCE I HAVE TO UPDATE MULTIPLE ROWS.
    I TRIED THIS SQL

    UPDATE TABLEA
    SET b = (SELECT b1 from TABLEB WHERE TABLEB.a1 = TABLEA.a )
    WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a)
    AND THE QUERY RETURNED ERROR ORA-01427 "single-row subquery returns more than one row"


    AGAIN I RAN THIS OTHER QUERY
    UPDATE TABLEA
    SET b = (SELECT b1 from TABLEB WHERE TABLEB.a1 = TABLEA.a )
    AND THESAME ERROR RETURNED.
    WHAT DO I DO?

    THANKS
    SOGA

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No; "SELECT 1" is part of a query whose task is to confirm whether there is at least one record that satisfies the WHERE condition. It could have been:
    - SELECT 1
    - SELECT NULL
    - SELECT 'Hello, World!'
    - ...
    For example:
    Code:
    SQL> select * from dept
      2  where exists (select 'Hello, World!' from emp
      3                where deptno = 10);
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> select * From dept
      2  where exists (select 1 from emp
      3                where deptno = -123);
    
    no rows selected
    
    SQL>

Posting Permissions

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