This discussion is archived
12 Replies Latest reply: Oct 12, 2012 12:51 PM by 927578 RSS

Why it's Showing Error

927578 Newbie
Currently Being Moderated
i want to update emp table sal into empl table. i wrote this query but not working..


Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
update empl a set a.sal=(select b.sal from emp b where a.deptno=b.deptno)


ORA-01427: single-row subquery returns more than one row


MERGE INTO empl a
USING emp b
ON (a.deptno=b.deptno)
WHEN MATCHED THEN
UPDATE SET a.sal=b.sal;

ORA-30926: unable to get a stable set of rows in the source tables
Pls Help

Edited by: 924575 on Oct 12, 2012 12:25 PM --Added Version                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 1. Re: Why it's Showing Error
    sb92075 Guru
    Currently Being Moderated
    924575 wrote:
    i want to update emp table sal into empl table. i wrote this query but not working..
    update empl a set a.sal=(select b.sal from ven_emp b where a.deptno=b.deptno)
    
    
    ORA-01427: single-row subquery returns more than one row
    
    
    MERGE INTO empl a
    USING emp b
    ON (a.deptno=b.deptno)
    WHEN MATCHED THEN
    UPDATE SET a.sal=b.sal;
    
    ORA-30926: unable to get a stable set of rows in the source tables
    Pls Help
    explain in English what you want the results to be.

    On the surface the SQL make NO sense which is why error get thrown.

    post Test Case data & then show desired results & how/why those results are determined.
  • 2. Re: Why it's Showing Error
    Solomon Yakobson Guru
    Currently Being Moderated
    Are you sure you didn't mean:
    update empl a set a.sal=(select b.sal from ven_emp b where a.<font color=red>empno</font>=b.<font color=red>empno</font>)
    SY.
  • 3. Re: Why it's Showing Error
    927578 Newbie
    Currently Being Moderated
    i want to update sal from emp table to empl table..

    select sal,deptno from emp;
    
    SAL     DEPTNO
    1300     10
    2450     10
    5000     10
    3000     20
    800     20
    3000     20
    2975     20
    1100     20
    1600     30
    950     30
    1250     30
    2850     30
    1250     30
    1500     30
    
    select sal,deptno from empl;
    
    SAL     DEPTNO
    -     10
    -     10
    -     10
    -     20
    -     20
    -     20
    -     20
    -     20
    -     30
    -     30
    -     30
    -     30
    -     30
    -     30
    
    After update 
    
    select sal,deptno from empl;
    
    
    SAL     DEPTNO
    1300     10
    2450     10
    5000     10
    3000     20
    800     20
    3000     20
    2975     20
    1100     20
    1600     30
    950     30
    1250     30
    2850     30
    1250     30
    1500     30
    i hope u understand this.
  • 4. Re: Why it's Showing Error
    927578 Newbie
    Currently Being Moderated
    Solomon Yakobson wrote:
    Are you sure you didn't mean:
    update empl a set a.sal=(select b.sal from ven_emp b where a.<font color=red>empno</font>=b.<font color=red>empno</font>)
    SY.
    Great..Working fine.. But can you pls help why MERGE is not working ?
  • 5. Re: Why it's Showing Error
    Solomon Yakobson Guru
    Currently Being Moderated
    924575 wrote:
    Great..Working fine.. But can you pls help why MERGE is not working ?
    For the same reason.

    SY.
  • 6. Re: Why it's Showing Error
    sudher Newbie
    Currently Being Moderated
    Hi,


    update empl a set a.sal=(select b.sal from ven_emp b where a.deptno=b.deptno)


    Kindly confirm before joining that there are no duplicate values in ven_emp b for every b.deptno.

    Could you provide that data of the two tables for studying the issue..

    Regards,
    Sudher.
  • 7. Re: Why it's Showing Error
    927578 Newbie
    Currently Being Moderated
    Thank you so much..

    Sorry to asking again
    update (select e.sal,b.sal from empl e,emp b where e.empno=b.empno) set e.sal=b.sal;
    
    ORA-00904: "B"."SAL": invalid identifier
    Why this not working ?
  • 8. Re: Why it's Showing Error
    560009 Newbie
    Currently Being Moderated
    try

    MERGE INTO emp1 a
    USING emp b
    ON (a.empno=b.empno)
    WHEN MATCHED THEN
    UPDATE SET a.sal=b.sal;
  • 9. Re: Why it's Showing Error
    Solomon Yakobson Guru
    Currently Being Moderated
    924575 wrote:
    Why this not working ?
    update (select e.sal e_sal,b.sal b_sal from empl e,emp b where e.empno=b.empno) set e_sal=b_sal;
    SY.
  • 10. Re: Why it's Showing Error
    927578 Newbie
    Currently Being Moderated
    Still Error ?
    update (select e.sal e_sal,b.sal b_sal from empl e,emp b where e.empno=b.empno)
     set e_sal=b_sal;
    
    ORA-01779: cannot modify a column which maps to a non key-preserved table
  • 11. Re: Why it's Showing Error
    Solomon Yakobson Guru
    Currently Being Moderated
    It means there is no unique key defined on emp:
    SQL> create table emp1 as select * from emp
      2  /
    
    Table created.
    
    SQL> create table emp2 as select * from emp
      2  /
    
    Table created.
    
    SQL> update (select e.sal e_sal,b.sal b_sal from emp1 e,emp2 b where e.empno=b.empno)
      2     set e_sal=b_sal
      3  /
       set e_sal=b_sal
           *
    ERROR at line 2:
    ORA-01779: cannot modify a column which maps to a non key-preserved table
    
    
    SQL> alter table emp2
      2    add constraint emp2_pk
      3      primary key(empno)
      4  /
    
    Table altered.
    
    SQL> update (select e.sal e_sal,b.sal b_sal from emp1 e,emp2 b where e.empno=b.empno)
      2     set e_sal=b_sal
      3  /
    
    14 rows updated.
    
    SQL> 
    SY.
  • 12. Re: Why it's Showing Error
    927578 Newbie
    Currently Being Moderated
    Thank you so much Solomon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points