7 Replies Latest reply: Nov 13, 2012 10:21 PM by 894936 RSS

    These are not working

    894936
      update temp t
      set t.ename=(select s.ename from emp s where t.deptno=s.deptno)
      
      merge into temp t
      using
      (select deptno,ename from emp ) s
      on
      (
      t.deptno=s.deptno
      )
      when matched then 
      update
      set
      t.ename=s.ename
      
      select s.deptno,s.ename from emp s,temp t
      where t.deptno=s.deptno
      
      create table temp 
      as
      select deptno,'new_'||ename from emp
        • 1. Re: These are not working
          Manguilibe KAO
          What error message(s) did you get?
          • 2. Re: These are not working
            Chanchal Wankhade
            Specify Your requirement correctly.
            Past you error msg.
            • 3. Re: These are not working
              the_slk
              Try now genius :-P

              --1
              CREATE TABLE TEMP AS
              SELECT DEPTNO,'new_'||ENAME
              FROM EMP;

              --some INSERT statement

              --2
              MERGE INTO TEMP T
              USING
              (
                   SELECT DEPTNO,ENAME
                   FROM EMP
              ) S
              ON
              (
                   T.DEPTNO = S.DEPTNO
              )
              WHEN MATCHED THEN UPDATE
              SET T.ENAME = S.ENAME;

              --3
              UPDATE TEMP T
              SET T.ENAME = (SELECT S.ENAME FROM EMP S WHERE T.DEPTNO=S.DEPTNO);

              --4
              SELECT S.DEPTNO,S.ENAME
              FROM EMP S,TEMP T
              WHERE T.DEPTNO = S.DEPTNO;
              • 4. Re: These are not working
                Solomon Yakobson
                slkLinuxUser wrote:
                Try now genius :-P
                Well, did you try it yourself, genius :-P? Your merge is no different from OP's. It literally says: take a row in EMP table and find a match by deptno in TEMP table then update ENAME from a match. Sounds OK, right? Just one "little" wrinkle - there are multiple matches. So obvuously MERGE will fail with ORA-30926: unable to get a stable set of rows in the source tables. It is no different from OP's:
                update temp t
                set t.ename=(select s.ename from emp s where t.deptno=s.deptno)
                /
                where we same way get multiple matches, just error message is different: ORA-01427: single-row subquery returns more than one row.

                So to summarize both UPDATE and MERGE have no logical sense.

                SY.
                • 5. Re: These are not working
                  rp0428
                  Query 1 won't work either unless you provide an alias (ENAME) for the newly concatenated column.
                  • 6. Re: These are not working
                    Solomon Yakobson
                    rp0428 wrote:
                    Query 1 won't work either unless you provide an alias (ENAME) for the newly concatenated column.
                    Yes you do have to alias CTAS select list expressions. But this is just small potatos and takes nothing to fix. A I already noted without correlating TEMP to EMP one to one it would not change much:
                    SQL> CREATE TABLE TEMP AS
                      2  SELECT DEPTNO,'new_'||ENAME ename
                      3  FROM EMP;
                    
                    Table created.
                    
                    SQL> MERGE INTO TEMP T
                      2  USING
                      3  (
                      4  SELECT DEPTNO,ENAME
                      5  FROM EMP
                      6  ) S
                      7  ON
                      8  (
                      9  T.DEPTNO = S.DEPTNO
                     10  )
                     11  WHEN MATCHED THEN UPDATE
                     12  SET T.ENAME = S.ENAME;
                    MERGE INTO TEMP T
                               *
                    ERROR at line 1:
                    ORA-30926: unable to get a stable set of rows in the source tables
                    
                    
                    SQL> update temp t
                      2  set t.ename=(select s.ename from emp s where t.deptno=s.deptno)
                      3  where t.deptno in (select deptno from emp)
                      4  /
                    set t.ename=(select s.ename from emp s where t.deptno=s.deptno)
                                 *
                    ERROR at line 2:
                    ORA-01427: single-row subquery returns more than one row
                    
                    SQL> DROP TABLE TEMP
                      2  /
                    
                    Table dropped.
                    
                    SQL> CREATE TABLE TEMP AS
                      2  SELECT EMPNO,'new_'||ENAME ename
                      3  FROM EMP;
                    
                    Table created.
                    
                    SQL> MERGE INTO TEMP T
                      2  USING
                      3  (
                      4  SELECT EMPNO,ENAME
                      5  FROM EMP
                      6  ) S
                      7  ON
                      8  (
                      9  T.EMPNO = S.EMPNO
                     10  )
                     11  WHEN MATCHED THEN UPDATE
                     12  SET T.ENAME = S.ENAME;
                    
                    14 rows merged.
                    
                    SQL> update temp t
                      2  set t.ename=(select s.ename from emp s where t.empno = s.empno)
                      3  where t.empno in (select empno from emp)
                      4  /
                    
                    14 rows updated.
                    
                    SQL>
                    SY.
                    • 7. Re: These are not working
                      894936
                      Hi Team,
                      Thanks for your support.......