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

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

---

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?

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.

---

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

---