-
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);
-
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?
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|