Question on Bulk Update

Hi,

We have a table with 200 Columns and 200,000 rows.

The Table looks something like this

table_a

col1
col2
col3

I am trying to update col3 with (col1-col2) value for all the 200,000 rows.

whats the most efficient way to do this. please suggest. I need to do a similar thing for atleast 50 columns.

Thanks

what´s wrong using a single update for a 200000 rows table....?

You can also write a procedure to update and commit after 10000 rows, just in case your rollback segment is small and you can not increase the size of it, but I would write one statement and update it after business hours.

Just a thought

Its not running oout of rollback segment. The problem is that it take around 25 minutes per update statement and i need to do 50 similar updates thats a total of 20 Hrs approx.

I am sure that there must be a better and faster way to do a similar thing.

Thanks

probably yea, create a copy, truncate the table and do insert /*+ APPEND */ select

but of course if you have 50 updates then you have to create like 50 temporary tables and truncate 50 times, and write 50 insert select where select you need all 200 columns :o

Quote:

---

Originally posted by ronnie
whats the most efficient way to do this. please suggest. I need to do a similar thing for atleast 50 columns.

---

50 columns of the same table? How about:

Code:

---

update table_a a
  set a.col3 = a.col1 + a.col2,
      a.col4 = a.col3 + a.col7,
      a.col34 = a.col69 + a.col54;

---

can you not do multiple updates at once, rather than waiting for one to finish before starting another?

Just create a PL/SQL procedure.

Quote:

---

Originally posted by irehman
Just create a PL/SQL procedure.

---

Uh oh, now you've said it.

Quote:

---

Originally posted by irehman
Just create a PL/SQL procedure.

---

Not unless you have to!!!!

But then, that's still better than manually doing 50 separate updates.

As marist89 pointed out, what's the difficulty in updating all the columns at once with a single statement?

- Chris

Quote:

---

Originally posted by marist89
50 columns of the same table? How about:

Code:

---

update table_a a
  set a.col3 = a.col1 + a.col2,
      a.col4 = a.col3 + a.col7,
      a.col34 = a.col69 + a.col54;

---

---

The reason I cannot have a single update statement is because we do divisions also and when the divisor is zero we want the result to be a null.


for example

update table_a a
set a.col3 = a.col1 + a.col2,
a.col4 = a.col3 * a.col7,
a.col34 = a.col69 / a.col54;

In the above example if col54 is zero or null the whole update will fail. We want the result to be a null in such a case. That is col34 should be a null when the divisor is zero.

Thanks

update table_a a
set a.col3 = a.col1 + a.col2,
a.col4 = a.col3 * a.col7,
a.col34 = a.col69 / decode(a.col54,0,null,a.col54);

so this is just one table with 50 columns to be updated. I thought it was 50 tables to be updated.

so, you can do all the columns at once and it should only take about 20 minutes, right?