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);

If you're using Oracle, please don't use the SQL Server or PostgreSQL tags. Those are different databases with different SQL dialects. — Justin Cave
– Justin Cave, Commented Mar 27, 2022 at 12:36

Justin Cave · Accepted Answer · 2022-03-27 12:40:14Z

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)

answered Mar 27, 2022 at 12:40

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Şükran Mamedov Over a year ago

thanks a lot for your answer, it really was useful for me)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related