updating columns by grouping transactions

Question

I have the following data in an object collection col_a:

00004719~BBK~US
00004719~SBK~US
00004719~OBK~GB
00004719~IBK~DE
00004720~BBK~US
00004720~SBK~GB
00004725~IBK~IN

Col_a is defined in the database as:

create OR REPLACE TYPE col_a AS TABLE OF varchar2(100)

I have the requirement to update 4 columns in upd_tbl (col_bbk,col_sbk,col_ibk,col_obk) from the above data such that: if col_a contains BBK, then update col_bbk with the 3rd value of the ~ separator for the transaction; If col_a contains SBK, then update col_sbk similarly for the transaction. The transaction number is identified by the first occurance of ~ substring in col_a.

transaction_number is pk in upd_tbl and there is a one-to-many between upd_tbl and the collection data. But, the collection has transaction_number as part of its element.

transaction_number = regexp_substr(col_a, '[^~]+', 1, 1)

Output required: For transaction '00004719', col_bbk = US, col_SBK = US, col_obk = GB, col_ibk = DE.

I am currently processing it by row by row update of individual col_a values in a loop. Essentially, for the same transaction '00004719', the update is fired 4 times based on the code (SBK etc).

Is it possible to write this update once per transaction in a single shot ?

The below keeps erroring out with "sql command not properly ended".

UPDATE upd_tbl
    SET ctry_bbk = (CASE 
                 WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'BBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                 ELSE NULL
                END),
    ctry_sbk = CASE 
                  WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'SBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                  ELSE NULL
                 END,
    ctry_ibk = CASE 
                  WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'IBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                  ELSE NULL
                 END,
    ctry_obk = (CASE 
                  WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'OBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                  ELSE NULL
                 END)
    from (select column_value from table(col_a('00004719~BBK~US','00004719~SBK~US','00004719~IBK~GB','00004719~OBK~IN','00004720~BBK~US','00004720~SBK~RU','00004725~BBK~US'))) tam
    where upd_tbl.transaction_number = regexp_substr(tam.column_value, '[^~]+', 1, 1);

Answer 1

This is a pivot method:

 select 
  transaction, 
  "'BBK'",
  "'SBK'",
  "'OBK'",
  "'IBK'"
from (
  select 
  regexp_substr("col_a", '[^~]+', 1, 1) as transaction,
  regexp_substr("col_a", '[^~]+', 1, 2) as code,
  regexp_substr("col_a", '[^~]+', 1, 3) as country
  from Table1 t)
pivot 
( 
  MAX(country) for code in ('BBK','SBK','OBK','IBK')
);

Here's the fiddle i'm working with.

Special thanks to @Lawrence and @Bulat for their help in finishing the idea, I just needed the MAX to aggregate instead of COUNT on the pivot.

To create a multiple column update with the pivot, it would look like this:

I've made it merge and update the same table, but setting it to a different table is as easy as adjusting the first merge/update statement.

MERGE INTO Table1 t1
USING 
(   
  select 
    "transactionid", 
    "'BBK'",
    "'SBK'",
    "'OBK'",
    "'IBK'"
  from (
    select 
    regexp_substr("col_a", '[^~]+', 1, 1) as "transactionid",
    regexp_substr("col_a", '[^~]+', 1, 2) as code,
    regexp_substr("col_a", '[^~]+', 1, 3) as country
    from Table1 t)
  pivot 
  ( 
    MAX(country) for code in ('BBK','SBK','OBK','IBK')
  )

) ta ON (regexp_substr(t1."col_a", '[^~]+', 1, 1) = ta."transactionid" )
WHEN MATCHED THEN UPDATE 
    SET "col_bbk" = ta."'BBK'",
     "col_sbk" = ta."'SBK'",
     "col_obk" = ta."'OBK'",
     "col_ibk" = ta."'IBK'",
     "transactionid" = ta."transactionid";

Here is a fiddle with this merge update pivot at work.