Composite primary keys

[jilpangs]

Company
BusGroup
Pdate
BusType
Pctr_num
Dealer_num
Fund_num
Segment

These are the composite primary keys in my table.

I also have following columns in same table.
Daily_balance
Daily_accts
Avg Bal

Now,

We are going to update fund_num in this table.
For eg (Attached Excel for better picture)
Fund numbers 5 and 8 are going to be changed to 10(a new fund num, hence wont be in my table)

When i update 5 to 10, i wont get any contraint issues, cos 10 is a new fund num.... when i again update
8 to 10, there could be possibility for constraint violation. In that cases i need to add up daily balance, daily account and also calculate avg balance.
unlike daily balance and daily account , avg balance
cannot be added up directly...its needs recalculation.

I have given sample data and required output in attached excel...

Can anyone help me how to acheive them thru a procedure or by any means. I have 25 millions of records in this table.

Thanks
Raj

[Brian Crabtree]

Oi. Well, if pdate is a date field, then you shouldn't have a problem of it conflicting.

However, the only way to do what you want is to build a PL/SQL block to make these changes for you.

Exactly how many records do you need to combine in this way?

Brian

[jilpangs]

Yes Pdate is a date field but both 5 and 8 fund numbers will have same date when they are
merged as 10...so i'll be getting a conflict.

PL/SQL is the way to go. Is that possible to give a sample code , how to calculate avg balance if there is a conflict ..also i need to remove the record if its already present..
i'm not a expert in pl/sql. please help.

4714368 records to be updated.

Thanks
Raj

[Yogeeraj_1]

hi,

for PLSQL, have a look at the following URL:
http://oraclesvca2.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/toc.htm

it is a must read!

if you stil have any trouble with your PLSQL procedure, do let us know.

regards
Yogeeraj

[Yogeeraj_1]

hi again,

you also have examples of PLSQL procedures at this same site:
http://oraclesvca2.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/a_samps.htm#4582


regards
yogeeraj