database:Oracle 7.x
codingl/sql
table_size:125 million rows
table_name: ORDER_PROC

columns:
Name Null? Type
------------------------------- -------- ----
CUST_ID NOT NULL NUMBER(10)
DIV_CODE NOT NULL CHAR(2)
ORDER_ID NOT NULL CHAR(8)
LINE_NBR NOT NULL NUMBER(4)
TOTAL_DEMAND_AMT NUMBER(9,2)
ORDER_DISC_AMT NUMBER(9,2)
DISC_AMT NUMBER(9,2)
ORDER_DEMAND_AMT NUMBER(9,2)
INSERT_DATE DATE

Requirement: Update disc_amt
Formula: disc_amt=(total_demand_amt/order_demand_amt)*order_disc_amt
Condition: Order_disc_amt > 0

Read a summary of order_proc information into a cursor (summarize at cust_id, div_code, order_id level) Summarize total demand amt (line_nbr) and order_disc_amt (order_id). This summary data is required in updating.

I am Unix & SQL guy. I am not a pl/sql coding person. I know very little about cursors. struggled a week with my limited knowledge, now asking for help from a pl/sql expert. Any questions...? Thanking you in advance.

It is not clear to me whether the "Summarize..." sentence relates to the update, or is a separate requirement.

Assuming it is a separate requirement, the update would be simply:

UPDATE tablename
SET disc_amt=(total_demand_amt/order_demand_amt) * order_disc_amt
WHERE Order_disc_amt > 0;

I'm not sure I understand the next bit. Could be:

SELECT cust_id, div_code, order_id, SUM(total_demand_amt), SUM(order_disc_amt)
FROM tablename
GROUP BY cust_id, div_code, order_id;

... but that may not be quite right.

to andrewst,

Yes, Summarize relates to the update statement. First, read this summary of data into a cursor (inluding cust_id, div_code, order_id) and then update the corresponding disc_amt whose order_disc_amt > 0.



another different question:
And even if it is a direct update, can we update a table simply using a update statement who has 125+ million rows....?

I can't really follow the requirements for this update. Maybe you could post an example with data before and after?

Yes, you can update a 125+ million row table with an UPDATE statement. Depending on what percentage of rows in the table the UPDATE will modify, you may need a large rollback segment, but using a single UPDATE statement is much more efficient than using PL/SQL cursors and loops.