8 Replies Latest reply on Feb 20, 2008 3:50 PM by 604542

    Copying data from one table to another

    604542
      Table A
      col1
      col2
      col3
      col4
      col5
      col6

      pk(col1,col2)


      Table B
      col1
      col2
      col3
      col4
      col5
      col6
      col7
      col8
      col9

      pk(col1,col2)


      I want to copy the columns col4,col5 and col6 from Table A into col4, col5 and col6 on Table B.

      Can someone help with the query ? Its been a while and I can't seem to write what must be a basic query. I can
      use the pk to join the tables but after that I'm having a mental block.
        • 1. Re: Copying data from one table to another
          Satish Kandi
          Could you share what have you tried so far in this regard?

          It is a basic query (considering this is a simple structure and no hidden clues).
          • 2. Re: Copying data from one table to another
            502227
            Dear friend,
            why dont you try this,

            update tableb x
            set (col4,col5,col6)= (select col4,col5,col6 from tablea y where x.col1=y.col1 and x.col2=y.col2)

            regards,
            prawin.
            • 3. Re: Copying data from one table to another
              604542
              But I have to update more than one row so it is giving me single sub query returns more than one row. I need to loop through somehow.

              update tableb x
              set (col4,col5,col6) = (select y.col4,y.col5,y.col6 from tablea y where x.col1=y.col1 and x.col2=y.col2)
              • 4. Re: Copying data from one table to another
                601428
                not sure what exactly u r asking but this might help
                UPDATE table2 t2
                SET (col1, col2, col4, col5, col6) =
                (SELECT col1, col2, col4, col5, col6
                FROM table1 t1
                WHERE (t1.col1, t1.col2) NOT IN (SELECT col1, col2
                FROM table2))

                Not tested
                • 5. Re: Copying data from one table to another
                  604542
                  UPDATE tblPLMailingsFigures
                  SET (approvingemail,status,comment_,dateapproved) = (
                  select a.approvingemail,
                  a.status,
                  a.comment_,
                  a.dateapproved
                  from tblplmailingsapprovals a,tblplmailingsfigures f
                  where a.traderemail=f.traderemail
                  and a.cob=f.cobdate
                  )
                  WHERE EXISTS
                  ( SELECT a.approvingemail,a.status,a.comment_,a.dateapproved
                  FROM tblplmailingsapprovals a
                  WHERE a.traderemail=tblPLMailingsFigures.traderemail and a.cob=tblplmailingsfigures.cobdate);


                  This query gives me 'Single row subquery returns more than one row' @ line 3.
                  I'm basing the query from
                  http://www.techonthenet.com/sql/update.php
                  • 6. Re: Copying data from one table to another
                    546595
                    may be merge
                    http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
                    • 7. Re: Copying data from one table to another
                      546595
                      may be try this, you can use distinct in the select but not sure the combination of the column is going to be correct for you? please check.
                      UPDATE tblPLMailingsFigures f
                      SET (approvingemail,status,comment_,dateapproved)
                                     = (
                                         SELECT --DISTINCT
                                                a.approvingemail,
                                                a.status,
                                                a.comment_,
                                                a.dateapproved
                                         FROM tblplmailingsapprovals a
                                         WHERE a.traderemail=f.traderemail
                                         AND a.cob=f.cobdate
                                        )
                      WHERE EXISTS
                               ( SELECT 'x'
                                 FROM tblplmailingsapprovals a
                                 WHERE a.traderemail=f.traderemail AND a.cob=f.cobdate);
                      • 8. Re: Copying data from one table to another
                        604542
                        Can you explain the use of 'x'. Is the first part running for each row returned by the 'where exists' clause.