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

Thanks. But, what if the col_a values are in an object collection type ? I tried something like this. I could not fit my solution in the comments. So, have edited the question. — Casey, Sep 08 '14 at 19:04

score 0 · Accepted Answer · edited May 23 '17 at 12:05

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.

excellent. thanks. It works great. Didn't think of using pivot earlier...I made it work with the normal update as well: UPDATE upd_table1 SET (ctry_bbk,ctry_sbk,ctry_ibk,ctry_obk) = (the pivot query above) where transactionid = table1.transaction_number ); Is there any specific advantage of using the merge to update vs the typical update ? — Casey, Sep 09 '14 at 20:11
[According to some](http://dba.stackexchange.com/a/3066/28523), merge is the oracle way to do it. It also helps think upsert (insert/update) that is faster than a regular insert then update. — crthompson, Sep 09 '14 at 21:34
Thanks. This is an upvote for sure and I will mark it as such. In my case, there is no scope for insert and its only an update operation Hence, a simple update would suffice for now. If there is a performance bottleneck, I can always change it to a merge with only the update portion as you suggested. But, thanks. The pivot solution was great. — Casey, Sep 10 '14 at 14:32

updating columns by grouping transactions

1 Answers1