Dear Experts
Below is the migrated table from one source and we need to generate 3 digit unique number for ACCT_ID field
Iam having aroung 8 million records in the table ,it is get duplicate against each account_no and mobile_no
Please help to generate random unique number against account_no and mob
create table ACCT_TB
(
ACCOUNT_NO varchar2(20),
MOB_NUM varchar2(20),
ACCT_ID varchar2(3)
);
insert into ACCT_TB (ACCOUNT_NO,MOB_NUM) values(12456,9999);
insert into ACCT_TB (ACCOUNT_NO,MOB_NUM) values(78594,9999);
insert into ACCT_TB (ACCOUNT_NO,MOB_NUM) values(85426,9999);
INSERT INTO ACCT_TB (ACCOUNT_NO,MOB_NUM) VALUES(82645,9999);
INSERT INTO ACCT_TB (ACCOUNT_NO,MOB_NUM) VALUES(75684,9999);
insert into ACCT_TB (ACCOUNT_NO,MOB_NUM) values(95145,8888);
insert into ACCT_TB (ACCOUNT_NO,MOB_NUM) values(35426,8888);
insert into ACCT_TB (ACCOUNT_NO,MOB_NUM) values(28941,8888);
INSERT INTO ACCT_TB (ACCOUNT_NO,MOB_NUM) VALUES(58961,8888);
INSERT INTO ACCT_TB (ACCOUNT_NO,MOB_NUM) VALUES(52148,8888);
BEGIN
UPDATE ACCT_TB
set ACCT_ID =TRUNC(DBMS_RANDOM.value(100,999))
COMMIT;
end;
Sample Result i am getting now for few account and mob num
ACCOUNT_NO MOB_NUM ACCT_ID
12456 9999 856
78594 9999 624
85426 9999 364
82645 9999 958
75684 9999 856
Here 856 is duplicated against same mobile num.
Please help