Josi Josi - 7 months ago 18
SQL Question

Substitute in-loop update in Oracle

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

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.