1 2 Previous Next 28 Replies Latest reply: Apr 4, 2013 9:27 PM by yxes2013 RSS

    Updating 2 tables side by side

    yxes2013
      Hi all sqlplus experts,


      How do I update Table2(idno) with values of Table1(idno)?
      By the way there are other columns which i do not want to be touched.
      This is just to create a match parent-child table test data.

      Example:

      Table1

      idno
      ===
      100
      101
      102
      103


      Table2

      idno
      ===
      any no
      any no
      any no
      any no

      I want all idno values in Table2 to be identical with Table1. It can be any order as long as the value is 1 to 1.
      I just want it populated for test data purposes.


      Thanks a lot,

      zxy
        • 1. Re: Updating 2 tables side by side
          Nimish Garg
          in your case :)
          delete from table2;
          
          insert into table2 select idno from table1;
          • 2. Re: Updating 2 tables side by side
            yxes2013
            i mean there are other columns, not to be touched. I just want the primary keys updated coz i want to make test parent-child data. :(
            • 3. Re: Updating 2 tables side by side
              BEDE
              I expect there should be a join between the two tables. But you show us only one column in both tables.
              Is it that both tables have only one column?
              If so, then:
              delete from table2;
              insert into table 2 select * from table1;
              commit;
              But I believe there's more to it and there should be more than just one column in the 2 tables and there should be a relation between the 2. Otherwise, what would those 2 tables be good for? Or is it that you are just playing without having any well-defined goal?
              • 4. Re: Updating 2 tables side by side
                yxes2013
                I told you using "update" :(

                By the way there are other columns which i do not want to be touched.
                This is just to create a match parent-child table test data.

                Edited by: yxes2013 on 4.4.2013 4:02
                • 5. Re: Updating 2 tables side by side
                  BEDE
                  Pray tell what the other columns are and how is one record from table2 to be related to a record from table1.

                  Usually it would be something like:
                  update table2 dest set 
                    idno=(select t.idno from table1 t where t.code_x=dest.code_x);
                  That is admitting that code_x is the column by which the 2 tables are to relate. But there may be more than one column, which would demand more conditions in the where clause...
                  Cant' tell more, having what you have shown us so far.
                  • 6. Re: Updating 2 tables side by side
                    pollywog
                    select * from table1;
                    
                    PK     IDNO
                    1     100
                    2     101
                    3     102
                    4     103
                    5     104
                    select * from table2
                    
                    PK     IDNO
                    1     
                    2     
                    3     
                    4     
                    5     
                    merge into table2 a
                    using (select * from table1 )b
                    on (a.pk = b.pk)
                    when matched then update 
                    set a.idno = b.idno;
                    select * from table2;
                    
                    PK     IDNO
                    1     100
                    2     101
                    3     102
                    4     103
                    5     104
                    • 7. Re: Updating 2 tables side by side
                      yxes2013
                      Thanks dear ...but there is no relating thing :(

                      Can I use rownum instead ?
                      • 8. Re: Updating 2 tables side by side
                        Karthick_Arp
                        So if you dont have any joning condition between the two table. And you just want to dump the value from one table to another. You can do this
                        SQL> select * from t1;
                         
                                NO
                        ----------
                                 1
                                 2
                                 3
                                 4
                                 5
                                 6
                                 7
                                 8
                                 9
                                10
                         
                        10 rows selected.
                         
                        
                        SQL> set null null
                        SQL> 
                        SQL> select * from t2;
                         
                                NO
                        ----------
                        null
                        null
                        null
                        null
                        null
                        null
                        null
                        null
                        null
                        null
                         
                        10 rows selected.
                         
                        SQL> begin
                          2    for i in (select no from t1)
                          3    loop
                          4       update t2 set t2.no = i.no where t2.no is null and rownum <2; 
                          5    end loop;
                          6  end;
                          7  /
                         
                        PL/SQL procedure successfully completed.
                         
                        SQL> select * from t2;
                         
                                NO
                        ----------
                                 1
                                 2
                                 3
                                 4
                                 5
                                 6
                                 7
                                 8
                                 9
                                10
                         
                        10 rows selected.
                        But keep in mind RDBMS is not designed to work like this.
                        • 9. Re: Updating 2 tables side by side
                          yxes2013
                          but my challenge is "sqlplus" not pl/sql :(

                          any other ideas? ;)

                          But keep in mind RDBMS is not designed to work like this.
                          I told you this is just to build test data. such that 2 tables becomes matched or related in IDNO.
                          • 10. Re: Updating 2 tables side by side
                            Karthick_Arp
                            yxes2013 wrote:
                            but my challenge is "sqlplus" not pl/sql :(
                            SQL Plus is a client tool. I have also done it using SQL Plus only. Or you trying to say you want it to be done in single SQL?
                            • 11. Re: Updating 2 tables side by side
                              yxes2013
                              yes dear single sql only :)

                              because pl/sql is slower than sqlplus.

                              I am updating millions of rows for testing.

                              By the way....the idno in Table2 is not really null, it can be any number.
                              • 12. Re: Updating 2 tables side by side
                                yxes2013
                                All of you failed :( huhuhuh

                                Any splplus experts here?
                                SQL> update signature2 x set
                                  acrnumber=(select y.acrnumber from pictures2 y where x.rownum=y.rownum);  2
                                  acrnumber=(select y.acrnumber from pictures2 y where x.rownum=y.rownum)
                                                                                         *
                                ERROR at line 2:
                                ORA-01747: invalid user.table.column, table.column, or column specification
                                
                                
                                SQL> merge into pictures2 a
                                using (select acrnumber from signature2 )b
                                on (a.rownum = b.rownum)
                                when matched then update
                                set a.acrnumber = b.acrnumber;  2    3    4    5
                                on (a.rownum = b.rownum)
                                      *
                                ERROR at line 3:
                                ORA-01747: invalid user.table.column, table.column, or column specification
                                Edited by: yxes2013 on 4.4.2013 4:37
                                • 13. Re: Updating 2 tables side by side
                                  Karthick_Arp
                                  yxes2013 wrote:
                                  yes dear single sql only :)

                                  because pl/sql is slower than sqlplus.
                                  Again SQL != SQLPlus. SQL (Structured Query Language) is a programming language and SQLPlus is a client tool like Toad. So Stop calling SQL as SQLPlus.
                                  I am updating millions of rows for testing.
                                  When you say you don't have a joining condition it violates the basics of the set theory. So doing it in single SQL may not be possible.

                                  So yes its going to be slow. But the good news is from 10g on oracle does a array fetch of 100 by default in a FOR LOOP. So that can be a little better.
                                  • 14. Re: Updating 2 tables side by side
                                    EdStevens
                                    yxes2013 wrote:
                                    All of you failed :( huhuhuh
                                    How to win friends and influence people ...

                                    <snip>
                                    1 2 Previous Next