| Update columns of one table using another table [message #493200] |
Sun, 06 February 2011 12:18  |
 |
priyankt
Messages: 10
Registered: February 2011
|
Junior Member |
|
|
Hello Experts,
I am trying to update columns of TableA with the columns of TableB.
Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitly 
Please suggest simple and quick method for performing this operation.
Regards
Priyanka
|
|
|
|
|
|
|
|
|
|
| Re: Update columns of one table using another table [message #493253 is a reply to message #493204] |
Mon, 07 February 2011 02:08  |
|
priyankt
Messages: 10
Registered: February 2011
|
Junior Member |
|
|
Hello All,
Thanks a lot for reverting back.
I have now created indexes on both the tables (on code field), still there is not much change in the execution time.
SELECT COUNT(*)
i did a explain plan for
FROM tablea a
WHERE a.code IN (SELECT b.code
FROM tableb b
WHERE a.code = b.code)
SELECT COUNT(*)
FROM tablea a
WHERE a.code IN (SELECT b.code
FROM tableb b
WHERE a.code = b.code)
Gives 60,000 rows
I did a explain plan for query below
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Below are the results:
60969 UPDATE STATEMENT
UPDATE tablea
60969 TABLE ACCESS FULL tablea
685 TABLE ACCESS BY INDEX ROWID Tableb
274 INDEX RANGE SCAN Tableb_IDX1
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
