Dear Experts,
11.2.0.4.0
CREATE TABLE ACCT
(
custid VARCHAR2(20),
ACCTID VARCHAR2(3),
flag varchar2(1)
);
INSERT INTO ACCT VALUES('95624','101','P');
INSERT INTO ACCT VALUES('95624',NULL,'N');
insert into acct values('95624',NULL,'N');
INSERT INTO ACCT VALUES('62548','101','P');
INSERT INTO ACCT VALUES('62548',NULL,'N');
INSERT INTO ACCT VALUES('62548',NULL,'N');
Tried Block
BEGIN
FOR I IN
(
SELECT custid ,MAX(ACCTID) AS ACID FROM ACCT
GROUP BY custid
)
LOOP
UPDATE ACCT SET ACCTID=I.ACID+1
WHERE CUSTID=I.CUSTID
AND ACCTID IS NULL;
END LOOP;
END;
Output i am getting is given below
Here i want to insert next increment starting from 101 for each custid.
Desired result
Note below i had modified on sample table to show desired result.
In my live environment i have around 200 custid i need to generate unique 3 digit number to the respective custid
Please help.