cancel
Showing results for 
Search instead for 
Did you mean: 

Composite primary keys

jilpangs
Occasional Advisor

Composite primary keys

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
4 REPLIES
Brian Crabtree
Honored Contributor

Re: Composite primary keys

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
Occasional Advisor

Re: Composite primary keys

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
Honored Contributor

Re: Composite primary keys

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Composite primary keys

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)