Substitute in-loop update in Oracle

Question

Help me out if its possible :) I want to write a single update statement (loop-free) from this:

BEGIN 

  FOR CRD IN ( SELECT CARD_NO
               FROM CARDS )
  LOOP

    UPDATE CARD_NO_MAP SET CARD_NO_MAP.NEW_CARD_NO = CRD.CARD_NO
      WHERE ( CARD_NO_MAP.NEW_CARD_NO IS NULL )
        AND ( CARD_NO_MAP.PREFIX = CASE WHEN ( CRD.CARD_NO LIKE '123%' ) THEN 555
                                        WHEN ( CRD.CARD_NO LIKE '456%' ) THEN 666
                                                                         ELSE -1 
                                   END )
        AND ( ROWNUM = 1 );

  END LOOP;

END;

If it not clear enough what it does, i'll try to explain.

---

Edit:

At the beginning we have something like this:

CARD_NO_MAP:
PREFIX  CARD_NO NEW_CARD_NO
---------------------------
555     000000  NULL
555     111111  NULL
555     222222  NULL
555     333333  NULL
555     444444  NULL
555     555555  NULL
555     666666  NULL
666     111111  NULL
666     222222  NULL
666     333333  NULL
666     444444  NULL

CARDS:
CARD_NO
-----------
1231263
1234566
1236547
1236549
4564566
4560001
4561234

And the CARD_NO_MAP after the update:

PREFIX  CARD_NO NEW_CARD_NO
---------------------------
555     000000  1231263
555     111111  1234566
555     222222  1236547
555     333333  1236549
555     444444  NULL
555     555555  NULL
555     666666  NULL
666     111111  4564566
666     222222  4560001
666     333333  4561234
666     444444  NULL

Every CARDS.CARD_NO is unique as much as all the PREFIX || CARD_NO unique in the CARD_NO_MAP table. (Don't ask why its separeted...)

Answer 1

As different, and arguably simpler, approach is to use merge. You can't merge into a view, but you can create a mapping by joining the two tables and applying a dense rank analytic function to the result:

select cnm.prefix, cnm.card_no, c.card_no as new_card_no,
  dense_rank() over (partition by cnm.prefix order by c.card_no) rnk1,
  dense_rank() over (partition by c.card_no order by cnm.prefix, cnm.card_no) rnk2
from card_no_map cnm
join cards c
on case when c.card_no like '123%' then 555
        when c.card_no like '456%' then 666
        else -1 end = cnm.prefix
where cnm.new_card_no is null;

    PREFIX CARD_NO          NEW_CARD_NO            RNK1       RNK2
---------- ---------------- ---------------- ---------- ----------
       555 444444           1231263                   1          5
       555 333333           1231263                   1          4
       555 222222           1231263                   1          3
       555 111111           1231263                   1          2
       555 000000           1231263                   1          1
       555 555555           1231263                   1          6
       555 666666           1231263                   1          7
       555 000000           1234566                   2          1
       555 111111           1234566                   2          2
       555 222222           1234566                   2          3
       555 333333           1234566                   2          4
...
       666 222222           4564566                   3          3
       666 333333           4564566                   3          4

 40 rows selected 

... which with the same date generates 40 rows as each new card number is mapped to each old one based on the prefix calculation; but the analytic columns have unique combinations so you can then filter than to find the results where they match:

select prefix, card_no, new_card_no
from (
  select cnm.prefix, cnm.card_no, c.card_no as new_card_no,
    dense_rank() over (partition by cnm.prefix order by c.card_no) rnk1,
    dense_rank() over (partition by c.card_no order by cnm.prefix, cnm.card_no) rnk2
  from card_no_map cnm
  join cards c
  on case when c.card_no like '123%' then 555
          when c.card_no like '456%' then 666
          else -1 end = cnm.prefix
  where cnm.new_card_no is null
)
where rnk1 = rnk2;

    PREFIX CARD_NO          NEW_CARD_NO    
---------- ---------------- ----------------
       555 000000           1231263         
       555 111111           1234566         
       555 222222           1236547         
       555 333333           1236549         
       666 000000           4560001         
       666 111111           4561234         
       666 222222           4564566         

 7 rows selected 

... which looks more promising. That can then be used as the using clause in a merge:

merge into card_no_map cnm
using (
  select prefix, card_no, new_card_no
  from (
    select cnm.prefix, cnm.card_no, c.card_no as new_card_no,
      dense_rank() over (partition by cnm.prefix order by c.card_no) rnk1,
      dense_rank() over (partition by c.card_no order by cnm.prefix, cnm.card_no) rnk2
    from card_no_map cnm
    join cards c
    on case when c.card_no like '123%' then 555
            when c.card_no like '456%' then 666
            else -1 end = cnm.prefix
    where cnm.new_card_no is null
  )
  where rnk1 = rnk2
) tmp
on (tmp.prefix = cnm.prefix and tmp.card_no = cnm.card_no)
when matched then update set cnm.new_card_no = tmp.new_card_no;

7 rows merged.

select * from card_no_map;

    PREFIX CARD_NO          NEW_CARD_NO    
---------- ---------------- ----------------
       555 000000           1231263         
       555 111111           1234566         
       555 222222           1236547         
       555 333333           1236549         
       555 444444                           
       555 555555                           
       555 666666                           
       666 000000           4560001         
       666 111111           4561234         
       666 222222           4564566         
       666 333333                           

Or you can use the same subquery for a direct update:

update card_no_map cnm
set new_card_no = (
  select new_card_no
  from (
    select cnm.prefix, cnm.card_no, c.card_no as new_card_no,
      dense_rank() over (partition by cnm.prefix order by c.card_no) rnk1,
      dense_rank() over (partition by c.card_no order by cnm.prefix, cnm.card_no) rnk2
    from card_no_map cnm
    join cards c
    on case when c.card_no like '123%' then 555
            when c.card_no like '456%' then 666
            else -1 end = cnm.prefix
    where cnm.new_card_no is null
  ) t
  where t.rnk1 = t.rnk2
  and t.prefix = cnm.prefix
  and t.card_no = cnm.card_no
)
where cnm.new_card_no is null;

A similar analytic approach is being used to my other answer, but the resulting code (with merge or update) is arguably a bit simpler in that it doesn't have the CTEs; however it may use more resources as the first intermediate result set could be large.

Answer 2

Assigning a unique value from cards to each null row in card_no_map makes the correlated update a bit messy. You can't update or merge into a view, but you can have the correlation work out a match between the rows.

You can assign a nominal row number to each map, based on its prefix:

select rowid as rid,
  prefix,
  row_number() over (partition by prefix order by rowid) as rn
from card_no_map
where new_card_no is null

And you can assign a nominal row number to each card, based on the prefix it will match:

select card_no,
  case when card_no like '123%' then 555
    when card_no like '456%' then 666
    else -1 end  as prefix,
  row_number() over (partition by case when card_no like '123%' then 555
    when card_no like '456%' then 666
    else -1 end order by card_no) as rn
from cards

And you can then use those two queries as CTE (or inline views if you prefer) within the update subquery, join them on the prefix and nominal row number, and then correlate the result with the row being updated via its rowid:

update card_no_map cnm
set cnm.new_card_no = (
  with t1 as (
    select rowid as rid,
      prefix,
      row_number() over (partition by prefix order by card_no) as rn
    from card_no_map
    where new_card_no is null
  ),
  t2 as (
    select card_no,
      case when card_no like '123%' then 555
        when card_no like '456%' then 666
        else -1 end  as prefix,
      row_number() over (partition by case when card_no like '123%' then 555
        when card_no like '456%' then 666
        else -1 end order by card_no) as rn
    from cards
  )
  select t2.card_no
  from t1 join t2 on t2.prefix = t1.prefix and t2.rn = t1.rn
  where t1.rid = cnm.rowid
)
where cnm.new_card_no is null;

With the sample data you added to the question that ends up with:

select * from card_no_map;

    PREFIX CARD_NO          NEW_CARD_NO    
---------- ---------------- ----------------
       555 000000           1231263         
       555 111111           1234566         
       555 222222           1236547         
       555 333333           1236549         
       555 444444                           
       555 555555                           
       555 666666                           
       666 000000           4560001         
       666 111111           4561234         
       666 222222           4564566         
       666 333333                           

The order that the card numbers were assigned doesn't match your sample output but I've just based the ordering on rowid; if you have a better way to order them then change the row_number() to do that.

I feel like there's a simpler way to do this but it's eluding me at the moment... having to hit the map table again doesn't seem very efficient.