Oracle: Update based on cyclical select

Question

I am having difficulty figuring out oracle query for Following table, where i have to update based on following criteria.

Requirement:

Here key is account_number based on account_number ,if the account have more than one user_numbers and out of those user_numbers if any user_number is not associated to any other account other than key, update that user_number stat to 'I',and leave user_number which has multiple accounts(can have key plus other accounts) as it is.If account have only one use do-not do anything.

create table cust_account(user_number number(10),account_number number(10),stat char(1));

Insert into cust_account(user_number,account_number,stat)values(123,345,'A')
Insert into cust_account(user_number,account_number,stat)values(226,345,'A')
Insert into cust_account(user_number,account_number,stat)values(226,456,'A')
Insert into cust_account(user_number,account_number,stat)values(556,767,'A')

commit;

Input:

Expected Output; Here account_number 345 has two user_numbers and out of which 123 user has only one account so deactivated. And 767 has only one use so don't even select it while updating.

Thanks in advance for the help.

Answer 1

If you want an update query, then I would recommend a correlated subquery:

update cust_account ca
set stat = 'I'
where stat = 'A' 
  and exists (select 1 
              from cust_account ca1 
              where ca1.stat = 'A'
                and ca1.account_number = ca.account_number 
                and ca1.user_number > ca.user_number)

If you want a select, then use row_number():

select ca.*,
    case when stat = 'A' and row_number() over(partition by account_number, stat order by user_number desc) > 1
        then 'I'
        else stat
    end new_stat
from cust_account ca