1 2 3 Previous Next 32 Replies Latest reply on Aug 3, 2012 4:22 AM by 947624 Go to original post
      • 15. Re: How To Replace the column data of one table to another table
        947624
        Hello.,

        I have already posted, what problem is there in that.Check it Once Clearly!!

        That update statement returns more than One Values. it throws an error.
        • 16. Re: How To Replace the column data of one table to another table
          james.
          Sorry Rowid may not work. Try this with rownum but as I said it is risky since you don't have pirmary key.

          Basically I have copy of emp table to emp1 and changed name by adding 1 at the end.


          with t as
          ( select rownum a_rno, a.empno,a.ename from emp a ORDER BY a.ename)
          , t1 as ( select rownum b_rno, b.empno,b.ename from emp1 b ORDER BY b.ename )
          select t1.ename t1name,t1.empno t1empno, t.ename tempname, t.empno tempno from t,t1 where t.a_rno=t1.b_rno;


          T1NAME T1EMPNO TEMPNAME TEMPNO
          ---------------------------------------------------------------------
          KING1     7839     KING     7839
          BLAKE1     7698     BLAKE     7698
          CLARK1     7782     CLARK     7782
          JONES1     7566     JONES     7566
          SCOTT1     7788     SCOTT     7788
          FORD1     7902     FORD     7902
          SMITH1     7369     SMITH     7369
          ALLEN1     7499     ALLEN     7499
          WARD1     7521     WARD     7521
          MARTIN1     7654     MARTIN     7654
          TURNER1     7844     TURNER     7844
          ADAMS1     7876     ADAMS     7876
          JAMES1     7900     JAMES     7900
          MILLER1     7934     MILLER     7934

          Regards
          1 person found this helpful
          • 17. Re: How To Replace the column data of one table to another table
            stueckl
            Hello Vijayrajaram,

            please keep calm ... none of your examples seems to make any sense ...

            your first example said:
            When deptno in table1 is by 1 smaller than in table2 then join
            But you said: deptno must be the same in both tables ... which couldn't possibly work, because the deptno will hardly be unique :-(

            your second example gives two possible interpretions:
            1. when deptno of table1 and deptno of table2 sum up to 100 or 149 then join
            2. get the first table in "natural" order, get the second table in "natural" order ... if both have equal row numbers then join

            the latter could look like this ...
            prompt creating sample table t1
            create table t1 as
                  select 'Vijay' ename, 10 deptno  from dual
            union select 'Jai',         20         from dual
            union select 'Ranesh',      30         from dual
            union select 'Peter',       40         from dual
            union select 'Tom',         50         from dual;
            
            prompt creating sample table t2
            create table t2 as
                  select 'xx' ename, 90 deptno  from dual
            union select 'yy',       80         from dual
            union select 'zz',       70         from dual
            union select 'ff',       60         from dual
            union select 'mm',       99         from dual;
            
            prompt altering ename to varchar2(10 char)
            alter table t1 modify ename varchar2(10 char);
            alter table t2 modify ename varchar2(10 char);
            
            prompt showing sort order of rows in database ...
                      select * from t1
            union all select * from t2;
            
            prompt merging data ...
            merge into t2 t2 using (
             select i2.t2rowid, i1.ename
             from (select rownum t2row, it2.rowid t2rowid, it2.*  from t2 it2) i2
                   inner join (select rownum t1row, it1.*  from t1 it1) i1
                    on i1.t1row = i2.t2row) d
            on (d.t2rowid = t2.rowid)
            when matched then update set t2.ename = d.ename;
            
            prompt showing sort order of rows in database after merge ...
                      select * from t1
            union all select * from t2;
            But everything would be a lot easier if there is a sensible key for joining or at least a useful resp. stable sort order ...

            regards Peter
            • 18. Re: How To Replace the column data of one table to another table
              Brian Bontrager
              >
              Actually there is no relation between two tables.

              Can you explain with rowid ,how it is posssible?
              >
              You've said they are the same, and you've shown 2 examples where they are different, and now you admit there is no actual relation between the tables.
              With no definition of how to relate the rows in the first table to rows in the second there is no possible solution, other than randomly assigning different deptno values to names. That seems unlikely, so we have to conclude we still have incomplete requirements.

              Forget PL/SQL if you want the fastest performance. It involves extra processing yield the same result for what you likely need to do in this case. There are times when you have something SQL cannot handle, but that is becoming more rare. Until we see details that force us to go there, we'll stand by "do it in SQL" for this question.

              Primary Keys are the proper construct for almost everything you would be tempted to use a rowid for, so don't worry about rowids for now.

              Here is an example (untested... not at a database right now) of a model similar to what you are describing, demonstrating what others have suggested, and what you may (or may not) need.
              create table emp 
              (empno number primary key, 
               empname varchar2(20));
              
              insert into emp values (1,'Brian');
              insert into emp values (10,'Vijay');
              insert into emp values (20,'Ravi');
              insert into emp values (30,'Larry');
              commit;
              
              create table dept
              (deptno number primary key,
               deptname varchar2(20));
              
              insert into dept values (100,'IT');
              insert into dept values (110,'FINANCE');
              insert into dept values (120,'HR');
              commit;
              
              create table emp_dept
              (empno number, 
               deptno number,
               primary key (empno, deptno));
              
              insert into emp_dept values (1,100);
              insert into emp_dept values (10,100);
              insert into emp_dept values (20,110);
              insert into emp_dept values (30,120);
              commit;
              
              select e.empno,e.empname,d.deptno,d.deptname 
                from emp e,
                       dept d,
                       empt_edpt ed
               where ed.empno=e.empno
                   and ed.deptno=d.deptno;
              
              EMPNO  EMPNAME  DEPTNO   DEPTNAME
              -----  -------  ------  --------
              1      Brian    100     IT
              10     Vijay    100     IT
              20     Ravi     110     FINANCE
              30     Larry    120     HR
              
              create table emp1 as (select e.empname,d.deptno
                from emp e,
                       dept d,
                       empt_edpt ed
               where ed.empno=e.empno
                   and ed.deptno=d.deptno);
              
              select * from emp1;
              
              EMPNAME  DEPTNO
              -------  ------
              Brian    100
              Vijay    100
              Ravi     110
              Larry    120
              
              create table emp2
              ( empname varchar2(20),
                deptno number);
              
              insert into emp2 values ('xx',150);
              insert into emp2 values ('yy',150);
              insert into emp2 values ('zz',170);
              insert into emp2 values ('aa',180);
              commit;
              At this point we have a table that resembles your original example, and also includes the case you did not mention: that deptno may be duplicated. (Which led to the subquery returns more than one value error) At this point you ask "how do I get output like this:"
              EMPNAME  DEPTNO
              -------  ------
              Brian    150
              Vijay    150
              Ravi     170
              Larry    180
              We don't know what the translation from the old to the new department is (in a business sense) - we still need you to tell us that. Do you understand now the information we need to help you along?

              If the new deptno values in emp2 are NOT the same as in emp1, my earlier query will not help. If they ARE they will help as long as deptno is unique. We know now they are not.

              In this example, you need either a table that maps the old deptno to the new deptno, or a method of updating emp2 that is defined based on EMPNO instead of DEPTNO.
              • 19. Re: How To Replace the column data of one table to another table
                947624
                Hi Brian,

                Actually , one of my friend need that requirement.they have some junk data for testing and applying their thoughts on it performence wise!

                I did n't understand clearly, what he said. that's why it happen.

                Anyway Thank U ALL!!
                • 20. Re: How To Replace the column data of one table to another table
                  952120
                  use the below anonymous block, may be useful in ur issue......

                  BEGIN
                  update emp2
                  set deptno = NULL;
                  commit;
                  FOR i IN (select deptno from emp1)
                  loop
                  update emp2
                  set deptno = i.deptno
                  where deptno is null
                  and rownum =1;
                  commit;
                  end loop;
                  end;
                  1 person found this helpful
                  • 21. Re: How To Replace the column data of one table to another table
                    947624
                    Hi nagaChithanya.,

                    Thank You!! for your reply. But I did n't get wanted output.
                    • 22. Re: How To Replace the column data of one table to another table
                      Venkadesh Raja
                      Vijayrajaram wrote:
                      Hii,,

                      I already told,

                      suppose Table 1 having 5 records like

                      Table1:
                      =====

                      ename deptno
                      ----------- ------------
                      vijay 10
                      jai 20
                      mani 30
                      Chandra 40
                      Suren 50

                      Table 2:
                      =====

                      ename deptno
                      ---------- ------------
                      xx 90
                      yy 80
                      zz 70
                      tt 60
                      mm 99


                      Needed OutPut :-
                      ============

                      ename deptno
                      --------- ------------
                      vijay 90
                      jai 80
                      mani 70
                      chandra 60
                      Suren 99


                      ....Plz,

                      Thank You!!

                      Edited by: Vijayrajaram on Aug 2, 2012 1:39 AM

                      Edited by: Vijayrajaram on Aug 2, 2012 1:40 AM
                      this ?
                      with t as
                      (select ename,deptno,row_number() over(order by deptno asc) rn from tabl),t1 as
                      ( select ename,deptno,row_number() over(order by deptno desc) rn from tab2 )
                      select distinct t1.deptno,t.ename from t,t1 where t.rn=t1.rn order by deptno 
                      
                      
                      DEPTNO     ENAME
                      90     vijay
                      80     jai
                      70     Mani
                      60     Chandra
                      • 23. Re: How To Replace the column data of one table to another table
                        Venkadesh Raja
                        duplicate :(

                        Edited by: Venkadesh on Aug 2, 2012 7:34 AM
                        • 24. Re: How To Replace the column data of one table to another table
                          947624
                          Venkadesh, Can you please explain with UPDATE command!!

                          ThankQ!!
                          • 25. Re: How To Replace the column data of one table to another table
                            Venkadesh Raja
                            Vijayrajaram wrote:
                            Venkadesh, Can you please explain with UPDATE command!!

                            ThankQ!!
                            Sure.

                            Before UPDATE
                            select * from tabl;
                            
                            ENAME     DEPTNO
                            vijay     10
                            jai     20
                            Mani     30
                            Chandra     40
                            AFTER UPDATE
                            begin
                            for i in ( with t as
                            (select ename,deptno,row_number() over(order by deptno asc) rn from tabl),t1 as
                            ( select ename,deptno,row_number() over(order by deptno desc) rn from tab2 )
                            select distinct t1.deptno,t.ename from t,t1 where t.rn=t1.rn order by deptno )
                            loop
                            update tabl set deptno=i.deptno where ename=i.ename;
                            end loop;
                            end;
                            
                            Statement processed.
                            
                            
                            SELECT * FROM tabl;
                            
                            ENAME     DEPTNO
                            vijay     90
                            jai     80
                            Mani     70
                            Chandra     60
                            1 person found this helpful
                            • 26. Re: How To Replace the column data of one table to another table
                              947624
                              ThanQ Venkadesh!

                              Its Very Helpfull .

                              I have one more doubt, If my table having 1 million records, then Performence wise is it good?
                              • 27. Re: How To Replace the column data of one table to another table
                                Venkadesh Raja
                                Vijayrajaram wrote:
                                ThanQ Venkadesh!

                                Its Very Helpfull .

                                I have one more doubt, If my table having 1 million records, then Performence wise is it good?
                                PL SQL always slow Processing.So if you pl sql you cannot expect 100% performance.
                                • 28. Re: How To Replace the column data of one table to another table
                                  947624
                                  Venkadesh wrote:
                                  Vijayrajaram wrote:
                                  ThanQ Venkadesh!

                                  Its Very Helpfull .

                                  I have one more doubt, If my table having 1 million records, then Performence wise is it good?
                                  PL SQL always slow Processing.So if you pl sql you cannot expect 100% performance.
                                  ThanQ VenkaDesh!!

                                  Edited by: Vijayrajaram on Aug 3, 2012 8:54 AM
                                  • 29. Re: How To Replace the column data of one table to another table
                                    sb92075
                                    row by row is slow by slow!