Need help regarding updating one table from another

[swarajpanicker]

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

[gandolf989]

Straight SQL would work much faster than a cursor for loop.
You could try something like this.

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);

[tamilselvan]

Straight SQL would work much faster than a cursor for loop.
You could try something like this.

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?

[gandolf989]

Hmm, Where is the "WHERE" clause?

I composed some sample code to show that the update statement works and highlighted the WHERE clause.

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.

[gandolf989]

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.

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