Need to Update 20 million records of data [message #376664] |
Thu, 18 December 2008 03:44  |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a table T1 in database A and T2 in database B.
I need to update few columns in T2 from T1( both are in different databases). They have the same primary key. Please let me know how can efficiently can we do this.
|
|
|
|
|
|
|
Re: Need to Update 20 million records of data [message #376700 is a reply to message #376664] |
Thu, 18 December 2008 05:28   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Assuming the pk columns are indexed, just do something like this:
UPDATE table_2
SET <columns> == (SELECT <columns>
FROM table_1
WHERE table_1.pk_column = table_2.pk_column)
WHERE <list of conditions to identify the rows in Table 2 to be updated>
|
|
|
|
Re: Need to Update 20 million records of data [message #376707 is a reply to message #376703] |
Thu, 18 December 2008 05:53   |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi All,
Thnks For the reply.
If i proceed to Create a table as suggested by "Tom" in the above link.
When i am deleting the OLD TABLE what will happen to the Constriants ?? ( Foreign Key constriants).is it possible to delete the OLD TABLE ? since it will voilate the constriant.
If i delete by using CASCADE . what will happen to the child table ?.
Once if rename the NEW TABLE created as OLD TABLE will the constaints build automatically?..
Your's
Ravi
|
|
|
Re: Need to Update 20 million records of data [message #376715 is a reply to message #376707] |
Thu, 18 December 2008 06:15   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That's the problem with that method - you need to disable all the constraints that refer to the table to be updated, and then after the table has been recreated, you need to recreate all the indexes, triggers, comments and constraints that were on the original table.
|
|
|
Re: Need to Update 20 million records of data [message #378673 is a reply to message #376715] |
Wed, 31 December 2008 04:48   |
panyam
Messages: 146 Registered: May 2008
|
Senior Member |
|
|
Hi All ,
Agian i am facing the prob with respect to update of the table.
SQL> select * from t1;
COL1 COL2 COL3 D
---------- ---------- ---------- -
1 100 200 A
2 101 201 A
3 102 202 A
4 103 203 B
5 104 204 B
SQL> select * from t2;
COL1 COL2 COL3
---------- ---------- ----------
1
2
3
4
5
i am updating t2 with t1 contents as follows.
update t2 set (t2.col2,t2.col3) = ( select t1.col2,t1.col3
from t1
where t1.col1 = t2.col1)
now the problem is t2 is having around 25 Million Records which wil match with t1. The update process will take so much time.
I thought of adding a flag to "t1" so i can do multiple updates.
But since it is in production i can't alter the table.
Any other way by which we can do the update fast ?...
|
|
|
|