updating a column in a table& eliminating duplicate rows
From: yilmaz <yilmazay_at_yahoo.com>
Date: 26 Oct 2001 00:14:34 -0700
Message-ID: <b8a3c655.0110252314.71ecdeda_at_posting.google.com>
hi guys,
i have two problems, and they are not as simple as look like from the subject name. the first one is as follows: we have a huge table with 20 columns and more than 50000 rows which holds some old data. Another table with 12 columns and about 17000 rows, holding new data.
But most of these data belong to the same items as the older ones , but they have some new , updated data in some of their columns. Now the task is:
i have to gather all these data (two tables) so that the data in the newer table will replace the older ones if already exists, if not then simply will be inserted.
both of them has a unique field (which supposed to be unique but actually not) called totid. now i want to update a certain column with newer ones , if the newer column is not null. here the difficult part is, about 16000 of these rows are same as the older ones,so i need to make a large number of update process. As you might have experienced before update takes a long time, especially if there are more than 50000 rows.
i try to solve the problem by writing a java program which selects data from newer table, checks if null or not, if there is some data updates the corresponding row in the older table. It works, but it is too slow, i calculated , it needs 12 hours to update just one column, which is not acceptable. I can accept a few hours for a column updating process.i also tried batch updateing but didn't make much difference. Then i try to seek the solution in PL/sql. i used cursors to make recursive update process, but it stil takes too much time. Worse, once i start my procedure it doesn't show any sign of processing, i just wait hours to see the result. I embedded some dbms_output_put_line statemnets in my procedure so that i can see what is going on during the execution. My procedure succeded to compile, but at runtime still there are no outputs. can some one help me with this.I need the experienced guys experiences.Any idea , suggestions are welcome.We are working on a project , and the time is short, we need to finish this task as soon as possible. I need your help.
my second problem is even more difficult. i couldn't find a solution yet.
these 50000 rows have some items with the same totid , which was supposed to be primary key and unique. Since it is not, i can't define it as P.k, i can't define index to speed my process. any idea how to delete those duplicate rows?
i used select distinct totid,.... but it didn't work.Because those items with same totid have different data in other columns, so select distinct can't distinguish distinct rows. thanks for your taking time.
have a nice day :) Received on Fri Oct 26 2001 - 09:14:34 CEST
Date: 26 Oct 2001 00:14:34 -0700
Message-ID: <b8a3c655.0110252314.71ecdeda_at_posting.google.com>
hi guys,
i have two problems, and they are not as simple as look like from the subject name. the first one is as follows: we have a huge table with 20 columns and more than 50000 rows which holds some old data. Another table with 12 columns and about 17000 rows, holding new data.
But most of these data belong to the same items as the older ones , but they have some new , updated data in some of their columns. Now the task is:
i have to gather all these data (two tables) so that the data in the newer table will replace the older ones if already exists, if not then simply will be inserted.
both of them has a unique field (which supposed to be unique but actually not) called totid. now i want to update a certain column with newer ones , if the newer column is not null. here the difficult part is, about 16000 of these rows are same as the older ones,so i need to make a large number of update process. As you might have experienced before update takes a long time, especially if there are more than 50000 rows.
i try to solve the problem by writing a java program which selects data from newer table, checks if null or not, if there is some data updates the corresponding row in the older table. It works, but it is too slow, i calculated , it needs 12 hours to update just one column, which is not acceptable. I can accept a few hours for a column updating process.i also tried batch updateing but didn't make much difference. Then i try to seek the solution in PL/sql. i used cursors to make recursive update process, but it stil takes too much time. Worse, once i start my procedure it doesn't show any sign of processing, i just wait hours to see the result. I embedded some dbms_output_put_line statemnets in my procedure so that i can see what is going on during the execution. My procedure succeded to compile, but at runtime still there are no outputs. can some one help me with this.I need the experienced guys experiences.Any idea , suggestions are welcome.We are working on a project , and the time is short, we need to finish this task as soon as possible. I need your help.
my second problem is even more difficult. i couldn't find a solution yet.
these 50000 rows have some items with the same totid , which was supposed to be primary key and unique. Since it is not, i can't define it as P.k, i can't define index to speed my process. any idea how to delete those duplicate rows?
i used select distinct totid,.... but it didn't work.Because those items with same totid have different data in other columns, so select distinct can't distinguish distinct rows. thanks for your taking time.
have a nice day :) Received on Fri Oct 26 2001 - 09:14:34 CEST