Need help regarding updating one table from another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Need help regarding updating one table from another

  1. #1
    Join Date
    May 2007
    Posts
    1

    Need help regarding updating one table from another

    Hi All,

    i have a parent table from which i want to update one column in a child table.

    The parent table has 34 columns and child table has 12 columns.
    Matching key between 2 tables is a composite key consisting of 3 columns.

    Both the tables contain around 70000 records.

    I want to update one column of child table from parent table based on the composite matching key consisting of 3 columns.

    This matching key is the primary key for child table but not for parent table.

    Hence i cant update the child table using a single SQL command as there are multiple records in parent table for the matching key combination.

    Presently i am updating the child table using a CURSOR FOR LOOP. But this is taking hell lot of time around 3-4 hrs.

    Can somebody tell me an optimised way of doing this task.

    Thanking in Advance,
    Swaraj

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Straight SQL would work much faster than a cursor for loop.
    You could try something like this.


    Code:
    UPDATE child_table
       SET field1 = 
         ( SELECT newvalue 
             FROM parent_table p
            WHERE p.pkey1 = child_table.pkey1
              AND p.pkey2 = child_table.pkey2
              AND p.pkey3 = child_table.pkey3);
    this space intentionally left blank

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote Originally Posted by gandolf989
    Straight SQL would work much faster than a cursor for loop.
    You could try something like this.


    Code:
    UPDATE child_table
       SET field1 = 
         ( SELECT newvalue 
             FROM parent_table p
            WHERE p.pkey1 = child_table.pkey1
              AND p.pkey2 = child_table.pkey2
              AND p.pkey3 = child_table.pkey3);

    Hmm, Where is the "WHERE" clause?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Quote Originally Posted by tamilselvan
    Hmm, Where is the "WHERE" clause?
    I composed some sample code to show that the update statement works and highlighted the WHERE clause.

    Code:
    SQL> select version from v$instance;
    
    VERSION
    -----------------
    9.2.0.7.0
    
    SQL> CREATE TABLE t1
        AS SELECT rownum pkey, rownum val
             FROM dba_objects
            WHERE rownum < 11;
      2    3    4
    Table created.
    
    CREATE TABLE t2
        AS SELECT rownum pkey, rownum*2 val
             FROM dba_objects
            WHERE rownum < 11;
    SQL>   2    3    4
    Table created.
    
    SQL> UPDATE t1
       SET val =
         ( SELECT val
             FROM t2
            WHERE t2.pkey = t1.pkey );
      2    3    4    5
    10 rows updated.
    
    SQL> COMMIT;
    
    SELECT *
      FROM t1;
    
    Commit complete.
    
    SQL>   2
          PKEY        VAL
    ---------- ----------
             1          2
             2          4
             3          6
             4          8
             5         10
             6         12
             7         14
             8         16
             9         18
            10         20
    
    10 rows selected.
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Of course we know that the WHERE clause in the query is being applied to the update statement when we change the WHERE clause to WHERE 1=1 and get an error.

    Code:
    SQL> UPDATE t1
       SET val =
         ( SELECT val
             FROM t2
            WHERE 1=1 );
      2    3    4    5       ( SELECT val
           *
    ERROR at line 3:
    ORA-01427: single-row subquery returns more than one row
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width