Oracle SQL statement to update column values based on single row function

Question

I have a column in the table which I want to update based on single row function, but when I try it says

"single-row subquery returns more than one row"

How can I fix this problem??

update transaction_account
   set dr_card_number =(select rpad(
                                    lpad(
                                         substr(
                                                dr_card_number, 5, 8), 
                                                12, 
                                                '*'), 
                                                16, 
                                                '*'
                                    ) as mytable
                          from transaction_account);

Answer 1

Assuming that your goal is just to apply the lpad and rpad functions to the dr_card_number, you don't need a subquery. Just

update transaction_account 
   set dr_card_number = rpad (lpad (substr (dr_card_number, 5, 8), 
                                    12, ''), 
                              16, '')

If you use a subquery, you'd need some way to determine a single value to return for any given row in the outer query. Assuming that your table has a primary key, you'd do something like this

update transaction_account outer
   set dr_card_number = (select rpad (lpad (substr (dr_card_number, 5, 8), 
                                            12, ''), 
                                      16, '')
                           from transaction_account inner
                          where inner.primary_key = outer.primary_key)