12 Replies Latest reply: Oct 12, 2012 2:51 PM by 927578 RSS

    Why it's Showing Error

    927578
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                Thank you so much Solomon